In a previous blog I talked about the Sequence Container, and some of its uses. To continue with that theme I now bring you the For Loop Container.
The For Loop is one of two Loop containers available in SSIS. In my opinion it is easier to set up and use than the For Each Loop, but it is just as useful. The basic Function of the for loop is to loop over whatever tasks you put inside the container a predetermined number of times, or until a condition is met. The For Loop Container, as is true of all the containers in SSIS, supports transactions by setting the Transaction Option in the properties pane of the container to ?Required?, or ?Supported? if a parent container, or the package itself is set to ?Required?
There are three expressions that control the number of times the
loop executes in the For Loop container.
- The InitExpression is the first expression to be evaluated on the For Loop and is only evaluated once at the beginning. This expression is optional in the For Loop Container. It is evaluated before any work is done inside the loop. Typically you use it to set the initial value for the variable that will be used in the other expressions in the For Loop Container. You can also use it to initialize a variable that might be used in the workflow of the loop.
- The EvalExpression is the second expression evaluated when the loop first starts. This expression is not optional. It is also evaluated before any work is performed inside the container, and then evaluated at the beginning of each loop. This is the expression that determines if the loop continues or terminates. If the expression entered evaluates to TRUE, the loop executes again. If it evaluates to FALSE, the loop ends. Make sure to pay particular attention to this expression. I will admit that I have accidentally written an expression in the EvalExpression that evaluates to False right away and terminated the loop before any work was done, and it took me longer than it probably should have to figure out that the EvalExpression was the reason why it was wrong.
- The AssignExpression is the last expression used in the For Loop. It is used to change the value of the variable used in the EvalExpression. This expression is evaluated for each pass through the loop as well, but at the end of the workflow. This expression is optional.
Lets walk through setting up an example of the package. In this example we?ll create a loop that executes a given number of times.
Create a new package and add two variables to it, intStartVal and intEndVal.
Next add a For Loop Container to the package and open the editor. Assign the following values for the expressions:
That is all the configuring that is required for the For Loop Container. Now lets add a Script Task that will display a message box with the value of the intStartVal variable as the loop updates the value of that variable. Here is the code to do that:
Public Sub Main()
Once that is done the package is ready to execute.
Dts.TaskResult = ScriptResults.Success
Now that the EvalExpression @intStartVal <= @intEndVal evaluated to false the package ends. In Part 2 of SSIS For Loop Containers I?ll go a little deeper in using a For Loop with some real world examples. Until then?