For Loop container in SSIS with example

Today in this article i will explain about one of the containers For Loop container. For Loop container groups set of tasks together and allows you to looping through the tasks in your package which is similar like for loop in any programming language.

• You can a send a mail for set of people.
• You can loop through an SQL statement and perform certain action until end of the table

Drag and drop For Loop container from tool box on to Control flow design editor as shown in Figure 1.

Figure 1.

Double click on For Loop container task which takes us to configuration editor as shown in Figure 2.

Figure 2

Configuration editor has only two options For Loop and Expressions.

InitExpression: This will initialize the loop with specified values

EvalExpression: specifies an expression to stop the loop when the expression evaluates to false. This expression must evaluate to a Boolean.

AssignExpression: specifies an expression that changes a condition in the same way each time the loop iterates.Which is optional one.

We will create a simple example which deletes one row from table product at time

Steps to Follow:

Step1: execute below queries which creates product table with id and name columns and insert 6 rows.

create table product(prodid int,name nvarchar(10))

insert into product values (1, 'prod1');
insert into product values (2, 'prod2');
insert into product values (3, 'prod3');
insert into product values (4, 'prod4');
insert into product values (5, 'prod5');
insert into product values (6, 'prod6');

Step2: Drag and drop For loop container and configure as shown below

Step3: Drag and drop Execute SQL task inside For Loop container as shown below. Here, Execute SQL task will be executed until EvalExpression condition fails. In this example Execute SQL task will be executed 6 times.

 Step4: Configure Execute SQL task in such a way it should delete one row based up on prodid which matches with @count variable from product table.

For the purpose this example, Connection property is specified with the AdventureWorks connection where Product table is created and SQLSourceType is specified as Direct input and SQLStatement is delete from product where prodid = ?. ? is a parameter and which will be replaced with @count variable during run time. @count variable will be attached to ? parameter under Parameter Mapping tab.After configuration is done task editor should reflect like below Figure.

Step5: Execute the Package which deletes all 6 rows from product table If configuration is properly set. Refer Below figures where are displayed after execution of package.

ControlFlow Design editor  

ProgressBar After execution

NOTE: Please leave comment if you like the post