November 2, 2009

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.

SSIS For Loop Container

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 variable screen as below:

For Loop Container 2

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.

8 comments:

  1. Hi there - I have used a For loop container to pull out 5 rows of data from a table into flat file. Did the same as you have shown above for the counter and increment. however when I run the package - it executes the same script for 5 times so I end up getting the same 5 rows 5 times. Not sure where I have gone wrong, can you please help

    thanks

    ReplyDelete
  2. can anyone explain use of 'expressions' tab in 'for loop editor'??

    ReplyDelete
  3. Hi Sharry,

    Which expression explanation you are talking about? Here I have explained: InitExpression, EvalExpression, AssignExpression.

    Thanks,
    Tejas
    SQL Yoga

    ReplyDelete
  4. In Real time how For loop container works ?

    ReplyDelete
  5. Hi Hima,

    For loop container works in real time as, to process batch of records where each record has some different logic to be applied.

    It would be great if you describe your question in detail.

    Thanks,
    Tejas

    ReplyDelete