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.