Blog Detail

25 Nov 2009
Tejas Shah
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'.
SSIS For Loop Container editor

First, we need to create variables to run this package based on variables. We can create variables by: View -> Other Windows -> Variables. Please find the 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 the 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.

About me


Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.