April 6, 2012 at 10:27 am
I am trying to retrieve system mapping data for one of our integration software packages. The data maps Job Numbers to Project numbers for integration between systems. The software stores this mapping data in a SQL Server 2005 database as a XML file in a single field with an XML data type. Not sure why they choose to store it this way, but it’s not something I can change.
I am trying to write a package that that writes this data into an XML file. The next part of the package reads this XML file into a SQL table.
My problem is with the XML file generation. I have a OLE data source that reads the XML data. The query looks like this: SELECT [XMLValue] FROM [XMLMap]. This query only returns a single row.
I’m using a flat file destination to write the XML file. The package creates a Unicode file from this. However, its adding a “stray” character at the beginning of the file that causes it to fail as an XML file. If I open the file in notepad and change the encoding to ANSI, this stray character shows as a question mark. In Unicode, the stray character is not visible.
I don’t believe this stray character is part of the query result. If I run the query in SQL Management Studio and copy and paste the output to notepad, it saves fine (ANSI or Unicode encoding) as a XML file… no stray character. Also, if run the query directly to a text file using xp_cmdshell and bcp, it produces the file correctly without the stray character.
The XML data does not have encoding info. I tried adding that to the flat file destination… no luck. Same problem with the stray character.
I also made sure the Flat File Connection Manager for this file is set to Unicode. I’ve tried all the format settings (Delimited, fixed width, ragged right) with no luck.
Not sure if this matters, but the Collation of the database its reading from is SQL_Latin1_General_CP1_CI_AS
I attached the problem xml file the package is producing. I had to change the extension to txt for this site to allow me to upload it.
Any suggestions on this?
April 9, 2012 at 10:02 am
I wasn't able to solve this problem, but I did find a way around it. I put myself through a crash course in XQuery and figured out how to query the data in this field. Using the query, I was able to build a view which accomplishing the same thing as what my SSIS package would have... actually better since its real-time.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply