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.

Implementation:

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

MsgBox(strProduct)

Next

Next

Step 4: execute package.

Thanks for reading this blog post.

About these ads
  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 316 other followers

%d bloggers like this: