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.
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:
|
1
2
3
4
|
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:
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.
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.
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.
Add an expression task inside the ForEach Loop. In the expression editor, use the following expression:
@[User::intTotalValue] = @[User::intStock]* @[User::intListPrice] + @[User::intTotalValue]
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.
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.
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.
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.
