SQL SERVER SSIS - For Loop Container

Today, I am going to explain SQL SERVER SSIS, For Loop Container.

The For Loop container defines a repeating control flow in a package. The loop implementation is the same concept of the For looping structure in programming languages. In each repeat 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.

Let's take an example to easily understand how to use For Loop Container with SSIS. Here I take example to iterate ActiveX Task.

1. Select and Drag, For Loop Container, from Container Flow Items to designer surface and add ActiveX Script Task to run inside the loop.

2. To configure this container, right click on this and click on 'edit'.

 

First we need to create variables to run this package based on variables. We can create variables by: View -> Other Windows -> Variables. Please find variable screen as below:

Here, I specified both variables. Count and Increment, that I am going to use for this example. I specified value Count = 20. So loop will be executed 20 times.

 

Let's take a view how each properties are used:

For Loop Properties:

1. InitExpression: Type an Initialization Expression in the given textbox. Initialization ensures that we are starting by setting out increment counter to 1. Here I specified variable to 1.

2.EvalExpression: Type an Evaluation Expression in the  given textbox. For each iteration the evaluation expression checks to see if we have reached our maximum iteration count as set above when we defined @Counter. Here I specified that @Increment <= @Count, code inside the for loop will execute @Count times.

3. AssignExpression: Type an Assignment Expression in the given textbox. This is used to increment the counter by one for each iteration of the loop, otherwise the loop would never finish. Here I specified to increment variable by 1.

That's it. We have configured SSIS For Loop container.

Now when we execute this package, it will execute ActiveX Task, 20 times (as specified in variable count).

Let me know if you have any questions.