This is another example package. This one retrieves a resultset and stores it in a variable. It then uses a data flow with a custom script source to read each row of the resultset and output it as a delimited string. Why would you want to do this? Well, it enables you to treat a resultset generically from a data flow perspective. There is only a single “column” defined in the metadata. So this works well if you want to export an unknown number of columns from a resultset to a flat file.
First, use an Execute SQL to retrieve and store a resultset in a variable. Then execution should continue to the data flow.
A custom script component acts as the source for the data flow. When adding the script component, make sure to source as the type of the script. Select Inputs and Outputs, and add a single output column to the existing output, and set the type of the column to DT_STR and set the length to 1000 (or something long enough to hold your widest resultset).
Select the Script option next, and add the resultset variable to the ReadOnly Variables property. Click the Design Script button, and add the following to the CreateNewOutputRows function: (Script adapted from Jamie Thomson’s example at http://blogs.conchango.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx)
Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As New System.Data.DataTable
Dim dataRow As Data.DataRow
Dim delimitedValues As String
Dim i As Integer
oledbAdapter.Fill(dataTable, Me.Variables.resultset)
For Each dataRow In dataTable.Rows
OutputBuffer.AddRow()
delimitedValues = String.Empty
For i = 0 To dataRow.ItemArray.GetUpperBound(0)
delimitedValues = delimitedValues + dataRow.Item(i).ToString()
If i <> dataRow.ItemArray.GetUpperBound(0) Then
delimitedValues = delimitedValues + “|”
End If
Next
OutputBuffer.DelimitedRow = delimitedValues
Next
You’ll also need to add a reference to the System.XML assembly (Select Project..Add Reference from the toolbar). If you want to use a different delimiter, just change the constant “|”.
Now you can connect this to a flat file destination with a single column defined, and output your recordset to that without worrying about the number or type of columns.