FOR XML PATH Query results to file with SSIS

Question

In SSMS I've gotten my for xml path query written and it's beautiful.

I put it in an "Execute SQL Task" in the Control Flow, set the resultset to XML.

Now how to I get the results into an actual xml file that I can turn around and FTP to a third party?

This should have been so easy! I would put the XML into a variable but we are looking at a HUGE file, possibly 100mb+

Do I need to use a Script Task? (I'd like to avoid that if there is another option.)

Answer 1

You'd need to use a data flow task, rather than an execute SQL task, to join a source (SQL call) and destination (flat file connection).