MSBI # 22 – SSIS # 9 – Control Flow Tasks # 4 – For Loop Container Task

Continuing from my last post on SSIS Control flow task on :

MSBI # 14 – SSIS # 7 – Control Flow Tasks # 2- Details of Control Flow Task {Part – II}

In this post we are going to look at every aspects of For Loop Container Task.

We are covering following points in this article 

• Why For Loop Container Task is Use ?
• How For Loop Container Task is look like ?
• Features of For Loop Container Task
• How to use For Loop Container Task ?
• What is alternative for For Loop Container Task ?
• Demonstration of using For Loop Container Task ?
• Various Properties for For Loop Container Task ?
• Reference link for For Loop Container Task

Now lets understand one by one :

Why For Loop Container Task is Use ?

• Name of the task itself explains most of of it !! For Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
  In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• For loop task is the looping implementation of a task and also This task will evaluate an expression and loops through the process and until the evaluation goes to False.

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

1. An optional initialization expression that assigns values to the loop counters.

2. An evaluation expression that contains the expression used to test whether the loop should stop or continue.

3. An optional iteration expression that increments or decrements the loop counter.

How For Loop Container Task is look like ?

This is very first task in tab itself !!

Drag this out in your development plane

Features of For Loop Container Task

The following diagram shows a For Loop container with a Send Mail task. If the initialization expression is @Counter = 0, the evaluation expression is @Counter < 4, and the iteration expression is @Counter = @Counter + 1, the loop repeats four times and sends four e-mail messages.

 

How to use For Loop Container Task ?

Its very simple to use having actually three properties to set and its ready to use

1. InitExpression: Insert Initialization Expression here ,Initialization ensures that we are starting by setting out increment counter to 1.

2.EvalExpression: Insert Evaluation Expression here , For each iteration the evaluation expression checks to see if we have reached our maximum iteration count as set above when we defined @Counter.

3. AssignExpression: Insert an Assignment Expression here , This is used to increment the counter by one for each iteration of the loop, otherwise the loop would never finish.

 

What is alternative for For Loop Container Task ?

I think there is no alternative for this Directly ,If you ask still is there then there is we can call one package inside each other ..Think Think !!! 

Better not search for this

Demonstration of using For Loop Container Task ?

1. Start in the package Control Flow, with no object selected .
2. Right click the background in the package, and select Variables
3. Add a variable called LoopIteration with the Int32 data type in the package.

4. Add a For Loop Container to the package

5. Edit the For Loop Container by double-clicking it, or right-clicking it and choosing Edit.
6. Set the InitExpression to @[User::LoopIteration]=0
7. Set the EvalExpression to @[User::LoopIteration]<5 where 5 would is the number of loops       you want to run.
8.Set the AssignExpression to @[User::LoopIteration]=@[User::LoopIteration]+1
9.Your settings should now look like this:

We can Use For loop Container as Do While Loop too as explained in following post :

SQL Server SSIS Do While / Until Loop Emulation using a For Loop Container | BI Monkey

Various Properties for For Loop Container Task ?

Following are various configurable properties for For loop container

Things to remember For Loop task

This is default properties of for loop task , If in a Loop container,the contents of that loop fail, then the entire container is failed at the same time.

Every now and then we need to create loops that do not fail when the contents fail – for example because failure is permitted or handled within the container.

We can set this too by allowing a loop to continue even if its contents have failed is to set the ForceExecutionResult property to Success, and every iteration of the loop will be treated as successful, regardless of what happens during the loop.

Reference link for For Loop Container Task

For Loop Container : MSDN

SQLIS | For Loop Container Samples

MSDN :For loop container in SSIS

MSDN :For Loop Editor

How to: Configure a For Loop Container

How to: Set the Properties of a Task or Container

For more on only example

SQL Server Integration Services (SSIS) – Part 12 – For Loop task in SSIS Packages

Programming For Loop Container | SQL Lion

Microsoft SQL Server Integration Services: How to configure a For Loop Container

SQL-Server Blog of Ritesh Shah –Fight the fear of SQL with SQLHub.com: For Loop container example in SSIS with C# script

 

Thanks for visiting my blog !!

Hope you have understood various aspect of For Loop Container Task and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox

Link Resource # 16 : August 12 – August 15 « Dactylonomy of Web Resource