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.
