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.
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() ' MsgBox(Dts.Variables("intStartVal").Value) ' Dts.TaskResult = ScriptResults.Success End Sub
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?