Looping through dataset in SSIS
I have written quite a few SSIS article so far but two days back I have received an email from one of the user where he wants to iterate through dataset like we used to loop on each records of dataset in .NET. I thought to provide him solution in form of blog article so that everyone can get benefit of it.
I am going to use my AdventureWorks2012 database in SQL Server 2012 along with Visual Studio 2010 to demonstrate this article.
Let us assume that we have two tables called “Sales.SalesOrderHeader” and “Sales.SalesOrderDetails” in our “AdventureWorks2012” database. We have one OrderID “43659” and we want to change (update) orderQty filed. If SalesOrderDetailID is even, we want to change OrderQty to 2 otherwise we want to change it to 1. We have direct TSQL query to do this task for us but we will use ForEach loop container to do this task to demonstrate how dataset works in SSIS.
Create SELECT query in New Query Window of SQL Server Management Studio in Adventureworks2012 database.
SELECT SOH.SalesOrderID ,SOD.SalesOrderDetailID ,SOH.OrderDate ,SOH.DueDate ,SOD.ProductID ,SOD.OrderQty FROM sales.salesorderheader AS SOH JOIN sales.salesorderDetail AS SOD ON SOD.SalesOrderID=SOH.SalesOrderID WHERE SOH.SalesOrderID=43659
Create one SSIS project from Visual Studio 2010 and follow the steps given below:
1.) Create variables to contain resultset going to be return from above select query and also create variable to hold value of each field inside the foreach loop. Here is the screen capture of variable window. (you can open variable window by right click on “Control Flow” tab, you will find “Variables” option in popup menu)
2.) Create one Execute SQL Task and create one OLEDB connection inside that for Adventureworks2012 database also have the SELECT query copy from SSMS which we have created above and paste it there. Follow the screen capture.
3.) Take ForEach Loop Container and set the following Property.
4.) now move to “Variable Mappings” tab in same Foreach Loop Editor to set parameter mapping.
5.) now set Script task to check whether SalesOrderDetailID is even or odd.
Here is the script which you have to write down in “Main” after clicking on “Edit Script”.
// TODO: Add your code here
if ((int)Dts.Variables["SalesOrderDetailID"].Value % 2 == 0)
Dts.Variables["isOdd"].Value = true;
Dts.Variables["isOdd"].Value = false;
Dts.TaskResult = (int)ScriptResults.Success;
6.) Now create two SQL Task and connect it with Script task created above. One connector (“Precedence Constraint”) will have “true” value and another will have “false”. I have taken screen capture of first connector with true value.
7.) Now both the execute SQL task for update will have UPDATE query. First UPDATE will have OrdQty value 2 as static and second will have 1. I am taking screen shot of first SQL Task inside foreach loop.
8.) once we set all of the above value from “General” tab, we will click on “Parameter Mapping” to provide value of SalesOrderID and SalesOrderDetailID to UPDATE query. Here is the screen capture.
9.) Now when you finally execute package, it will looks like this (if it executed successfully):
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.