title: Using SSIS to extract a XML representation of table data to a file
body: <p>I'm trying to use SSIS to extract XML representation of a query result set to a text file.  My query is currently successfully extracting the exact XML output I need when I run it in SSMS.  I've tried every trick I can find to use this result set in a SSIS package to create a file.</p>
  
  <p>Using a dataflow to port a OLE Source to a Flat file doesn't work because the output of a XML query is treated as TEXT and SSIS can't push TEXT, NTEXT or IMAGE to a file destination.</p>
  
  <p>I've tried to then Execute SQL Task to fill a user variable and then use a Script Task (written using C#) to write the contents of this user variable to a file output, but the user variable is always empty.  I don't know, but I suspect this is, again, because the XML is treated as TEXT or IMAGE and the user variable doesn't handle this.</p>
  
  <p>The query is in this form:
  SELECT *
  FROM dataTable
  WHERE dataTable.FIELD = 'Value'
  FOR XML AUTO, ROOT('RootVal')</p>
  
  <p>The resulting dataset is well formed XML, but I can't figure out how to get it from result set to file.</p>
  
  <p>It's a relatively easy task for me to write a console app to do this in C# 4.0, but restrictions require me to at least prove it CAN'T be done with SSIS before I write the console app and a scheduler.</p>