FOR XML PATH Query results to file with SSIS

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 result开发者_StackOverflowset 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.)

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).

I was searching exact same issue (i.e. Huge XML data extraction from a stored procedure and writing out using SSIS).

I was using OLEDB and it was working but painfully slow. When switched to ADO.NET adaptor in SSIS, XML retrieval from SQL Server is 50 times faster. If you are interested, I'll describe the details.

I don't quite understand the hesitation with using a Script Task, since your entire script (VB.net) could look like this:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO


Public Sub Main()
 Using Out As StreamWriter = New StreamWriter(<--XML FILE LOCATION-->)
 Out.Write(Dts.Variables("User::Xml").Value.ToString())
 Out.Close()
 End Using
 Dts.TaskResult = Dts.Results.Success
 End Sub

That's pretty much all it takes as long as your XML is well-formed.