SSIS

Looping Through a Result Set with the ForEach Loop

Shawn Harrison Jun 24, 2015

Looping Through a Result Set with the ForEach Loop

In today's blog post, I am going to show you another use for the ForEach Loop container in SSIS. Most of the time, this container is used for looping through a group of files, however, there are many different uses for it.

Let's say you have a need to take some rows from a source and aggregate some values. Normally, something like that is done in the data flow, but it can also be done in the control flow.

First, create 5 variables. Pay close attention to the data types.

Table of with 5 rows of variables and three column headings of Name, Scope and Data type

In the control flow of an SSIS package, add an Execute SQL Task and open the editor. Set the 'Result Set' property to Full result set. Create an OLE DB connection to the AdventureWorks2012 database. Add the following T-SQL statement:

 
SELECT Name, SafetyStockLevel, ListPrice
From Production.Product
Where ListPrice < 20
AND ListPrice > 0

This query just pulls a small set of data from the Product table:

Screenshot of Execute SQL Task Editor window with "General" tab selected

Click the 'Result Set' option to the left. Set the Result Name to 0 and for the Variable Name, select objProductList. This variable will hold the results returned by the query. Click OK to close the editor.

Execute SQL Task Editor window with "Result Set" tab selected

Next, add a ForEach Loop container and connect the Execute SQL task to it. Open the editor and click Collection. In the enumerator drop down menu, select ForEach ADO Enumerator. In the ADO Object Source Variable drop down menu, select objProductList and for the Enumeration Mode, select Rows in the first table.

Screenshot of Foreach Loop Editor window with "Collection" tab selected

Open Variable Mappings. In the drop down menu for the first variable, select intStock, then for the next one, select intListPrice. Click OK to close the editor.

Screenshot of Foreach Loop Editor window with "Variable Mappings" tab selected

Add an expression task inside the ForEach Loop. In the expression editor, use the following expression:

@[User::intTotalValue] = @[User::intStock]* @[User::intListPrice] + @[User::intTotalValue]

Screenshot of Expression Builder window with the Variables and Parameters folder selected and open to show files and subfolders

This expression will multiply the number of stock with the list price value of each item and then add that to the value of the intTotalValue variable. Click OK to close the editor.

To test this out, right click the ForEach Loop and select 'Edit Breakpoints'. Select the last option; Break at the beginning of every iteration of the loop.

Screenshot of Set Breakpoints - Foreach Loop Container window open and "Break at the beginning of every iteration of the loop" selected

Now, execute the package. The package will stop and a watch window should open at the bottom of the screen. If it doesn't, click Debug > Windows > Watch 1. In the watch window, add the names of the variables you want to monitor. Keep in mind that the variable names are case sensitive here.

Each time the execution is continued, the variable values will change and the value of intTotalValue will increase. The interesting part about this is that you won't see the final value of intTotalValue. I actually drove myself insane trying to find out what was wrong with the expression, but there is nothing wrong at all. If there is a need to check the final value of the variable, a script task can be use to display the value.

In the Script task editor, select intTotalValue as the ReadOnlyVariable. Then, click Edit Script.

screenshot of Script Task Editor window open and Script selected from the menu and ReadOnlyVariables seclected

In the script editor, use the following script.

MessageBox.Show(Dts.Variables["FileName"].Value.ToString());

Close the script editor and then click OK to close the script task editor. Remove the breakpoint from the ForEach loop and execute the package. You should see a message box that displays the final value of intTotalValue.

screenshot of a message box window open displaying 7433

And there you have it. Again, this is something that is normally done in the data flow, but in case you ever encounter a need to do it at the control flow level, you know that it can be done.