title: SSIS: Script task to write recordset to file
body: <p>I am using SQL Server Data Tools 2013 to create an SSIS package. This package has an Execute SQL Task with a Full Resultset option to push the query results into an SSIS Variable, of type Object.</p>
  
  <p>I'm using the following in a script task to take a recordset stored in an object variable and write it to a CSV:</p>
  
  <pre><code>    Public Sub Main()
  
      Dim fileName As String = Dts.Variables("vFileName").Value.ToString
      Dim destinationPath As String = Dts.Variables("vDestinationPath").Value.ToString
      Dim destinationPathAndFileName As String = destinationPath + fileName
      Dim fileContents As String = ""
  
      Dim oleDB As OleDbDataAdapter = New OleDbDataAdapter()
      Dim table As DataTable = New DataTable()
      Dim rs As System.Object = Dts.Variables("vResultSet").Value
  
      ' Populate DataTable with vResultSet data
      oleDB.Fill(table, rs)
  
      ' Loop through columns and concatenate with commas
      For Each col As DataColumn In table.Columns
          fileContents &amp;= col.ColumnName &amp; ","
      Next
  
      ' Remove final comma from columns string and append line break
      fileContents = fileContents.Substring(0, fileContents.Length - 1)
      fileContents &amp;= Environment.NewLine
  
      ' Loop through rows and concatenate with commas
      Dim i As Integer
      For Each row As DataRow In table.Rows
          For i = 1 To table.Columns.Count
              fileContents &amp;= row(i - 1).ToString() &amp; ","
          Next
  
          ' Remove final comma from row string and append line break
          fileContents = fileContents.Substring(0, fileContents.Length - 1)
          fileContents &amp;= Environment.NewLine
  
      Next
  
      ' Write all text to destination file. If file exists, this step will overwrite it.
      System.IO.File.WriteAllText(destinationPathAndFileName, fileContents)
  
      Dts.TaskResult = ScriptResults.Success
  End Sub
  </code></pre>
  
  <p>This works, but it's veeeery slow, like 25+ minutes to write a single 14k-row dataset to CSV. I can't use a data flow because this process exists in a loop, and the metadata for each table to be exported is different. I'm pretty sure a script task is the only option, but is there a faster way than looping through each row of the dataset? Please let me know if I can provide more info.</p>