Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Paul_Harvey
Starting Member
3 Posts |
 Posted - 2009-12-07 : 05:32:40
|
| Hi All,I am new to this forum, and this is my first post.I have a problem with my SSIS package, whereby the XML Source component fails when there is an & character in it. Despite setting the failures to redirect the rows, the package is still failed, and terminates.I have tried to re-encode the XML file, but to no avail.Is there any way to do a find and replace in the XML files (which are in a foreach loop) to simply replace any & with "and"?Happy to provide the XML file if there is a way I can attach it.Any help gratefully received, as I have been tearing my hair out on this one! |
|
|
Sequin
Starting Member
25 Posts |
Posted - 2009-12-07 : 05:39:06
|
| Ampersands in XML should be replaced by & |
 |
|
|
Sequin
Starting Member
25 Posts |
Posted - 2009-12-07 : 05:39:47
|
Sorry - that should read & |
|
|
|
Sequin
Starting Member
25 Posts |
Posted - 2009-12-07 : 05:40:39
|
| It is replacing it every time! & amp ; (remove the spaces!) |
|
|
|
Paul_Harvey
Starting Member
3 Posts |
Posted - 2009-12-07 : 06:08:58
|
| The problem is that the XML files are produced by an external source, and I need to import the data into my database. For the most part this is fine, but once in a while the file contains an amperand, and then the package fails. The XML Source component fails.My current fix is to manually replace & with "and", then the SSIS package completes. I want to find some way to automate this process. |
|
|
|
smeeluv
Starting Member
20 Posts |
Posted - 2009-12-08 : 11:20:29
|
| Paul, I have never worked with XML but I would assume that it works just like everything else. Is it a particular field that contains the & sign? If so, is it possible to pull that XML file into a data flow task, add a derived function for that field to replace the & with and, then load the data back into another XML file and run the process off of that. |
|
|
|
Paul_Harvey
Starting Member
3 Posts |
Posted - 2009-12-09 : 03:29:26
|
| Hi smeeluvThe problem is that the xml file will not import into the dataflow task, and fails the package. It causes the XML Source component to fail. Therefore I think my only option is to somehow make the change BEFORE it hits the XML Source component. |
|
|
|
|
|
|
|
|
Find and Replace Text?