For Loop Container in SSIS

The For Loop Containers are used for repeating control flow in SSIS packages. The Loop implementation is very similar to the For Loop structure in programming languages. In each iteration of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop Container uses the following elements to define the loop:

• An optional initialization expression that assigns values to the loop counters.
• An evaluation expression that contains the expression used to test whether the loop should stop or continue.
• An optional iteration expression that increments or decrements the loop counter.

The following example shows a For Loop container displaying Current Value of Expression through Message Box.
1. To Begin, Open SSIS Project or create new Project through BIDS. Add a new package and rename it with ForLoopContainer.
2. Add two package variables - StartValue and EndValue, both of integer type.

3. Drag and drop For Loop Container from toolbox and double click on it to open For Loop Editor. Set InitExpression, EvalExpression, and AssignExpression with @StartValue, @StartValue <= @EndValue and @StartValue = @StartValue + 1 respectively as shown below:

This loop repeats five times because StartValue is set to 1 and EndValue is set to 5.
4. Drag and drop Script Task inside For Loop Container and double click to open Script Task Editor.
Type User::StartValue in ReadOnlyVariables property of script Task editor. Now Click on Edit Script... button to write code

5. Write following code in Main() function to display a message box for current value of StartValue variable in loop iteration:
MessageBox.Show("StartValue = " + Dts.Variables["User::StartValue"].Value.ToString());

Main function will look like this:


6. Close script task editor and save changes. Now right click on Package in Solution Explorer and click Execute to execute the package.

The package will show Message box 5 times before completion.

Thats all. We can apply any logic inside For Loop Container as per business requirement.