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

ProjectSMM.com

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 )

Create a global variable called objRS and set its Data Type to object
Create a Execute SQL Task
- In the General tab, set the ResultSet option to Full Result Set
- In the Result Set tab:
Create a Script Task

In the Script tab, set the ReadOnlyVariables property to objRS
Click on the button to open the Script Editor

On the Project Menu of Script IDE,click Add Reference
Scroll through the list of Components, elect System.XML.dll
Click then click to save the Reference

In the VB Code section, add the follwoing IMPORTS statments to the top of the page:

Imports System.Data.OleDB
Imports System.XML

Put the code below into the script task
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

				
			Next

			MsgBox(sMsg)

			sMsg = ""

			
		Next

		

		Dts.TaskResult = Dts.Results.Success

		
	End Sub

	
End Class

© Gonzo TechNet is a wholely owned subsidiary of Fly-By-Night LLC, a divison of Charge-A-Lot Consulting, hosted by ©Projectsmm.com
With many thanks to Scott M. McNair and numerous others too ashamed to allow their names to be mentioned.