SSIS and XML Output

I was pretty surprised by this and am in pretty good company.  SSIS 2005 and 2008 do not have an XML Destination adapter. We have another “opportunity” from Microsoft to work out a way to export XML data.  I’ll disagree that this is a good idea, despite those saying that it’s easy to work around in a script component.  I’m not a great SSIS Script writer.

My problem – I want to take a simple SQL query that uses FOR XML AUTO to generate XML and put that into a file.  If you set this as the only part of your source query, you end up with an output of DT_Image.  Converting that to text results in a long string of numbers. While I was amused at the result, it didn’t help me generate the necessary XML for our partners.

I came up with a relatively simple workaround that works well for me because I’m only dealing with one XML Data Set at a time.  I put something like the following in my OLEDB Source:



I was then able to pipe that into a Delimited Text File destination with a CRLF Delimiter and no column headers. That generated results I could pass on to our partners. While it’s not the most elegant solution, it was much easier to me than trying to write and maintain a script component just to handle XML.


One thought on “SSIS and XML Output

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s