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:

DECLARE @XMLOutput XML
SET @XMLOutput = (SELECT * FROM MyTable FOR XML AUTO)

SELECT CAST(@XMLOutput as VARCHAR(MAX)) as XMLResults

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.