Home > SSIS > Process resultset using object variable – SSIS – SQL Server

Process resultset using object variable – SSIS – SQL Server

In one of my blog, I discussed about iterating the package object variable using foreach loop container (reference).

In this blog I will be explaining about iterating package object variable with result set using .net object.  Script task component is a very useful component in SSIS to write .NET script to perform some action programmatically. So, I will use OLEDBDataAdapter object to read result set of an object variable.


Step 1: Create a package and create a variable with object scope. “rs” is my package variable.

Step 2: Add script task and connect to Adventureworks database with the below given query.

SELECT TOP 3 [Name] FROM [Production].[Product]

Step 3: Add a script task and connect Execute SQL Task with Script task

Add Read only variable in script task. In this example, I am using “rs” package variable.

Click Edit Script button and paste the below given code in the main() function in the script task.

Dim da AsNew OleDbDataAdapter()

Dim dt AsNew DataTable()

Dim row As DataRow

Dim col As DataColumn

Dim strProduct AsString

da.Fill(dt, Dts.Variables(“rs”).Value)

ForEach r As DataRow In dt.Rows

row = r

ForEach c As DataColumn In dt.Columns

col = c

strProduct = row(col.Ordinal).ToString




Step 4: execute package.

Thanks for reading this blog post.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: