Gonzo TechNet
Gonzo TechNet - How to shred/parse/iterate a recordset in an SSIS Script Task

Assume that in an SSIS package you have an Execute SQL Task that returns a Resultset/Recordset into a variable called objRecordset (e.g. select * from Orders) ... and now you want to access that data in a ScriptTask to perform some row by row operations. Here is the code snippet on how you can do it.
(Plagerized/modified from Nayan Patel's post )

  1. Create a global variable called objRS and set its Data Type to object
  2. Create a Execute SQL Task
  3. Create a Script Task
  4. Put the code below into the script task
  5. Close the IDE and then click on to close Script Task Editor DialogBox
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb

Public Class ScriptMain
Public Sub Main()
Dim oleDA As New OleDbDataAdapter
Dim dt As New DataTable
Dim col As DataColumn
Dim row As DataRow
Dim sMsg As String

oleDA.Fill(dt, Dts.Variables("objRecordset").Value)

For Each row In dt.Rows
For Each col In dt.Columns
sMsg = sMsg & col.ColumnName & ": " & row(col.Ordinal).ToString & vbCrLf
sMsg = ""

Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle