SSIS: Script task to write recordset to file
<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>