SQL Server Integration Services – For Loop Container


In my previous article we discussed about the Sequence container. In this article we will implement the For loop container. The objective of this container is that it will iterate through all the tasks inside until the condition is satisfied. For every  iteration the For Loop container evaluates the condition that is specified and repeats the process until the condition stands false.

The For loop container has 3 expressions that determines the number of executions that container would undergo. let us understand these expressions briefly.

InitExpression:

This is an optional expression in the For loop container. It is evaluated before any process is started inside the loop. The major use of this expression is to set an intial value as the name indicates.

EvalExpression:

The second expression when the loop starts. It is also evaluated before any process is executed inside the container, and then again evaluated at the beginning of each loop. This determines if the loop continues or ends.
If the condition stands TRUE, the process is executed again. If the condition stands FALSE, the loop ends.

AssignExpression:

The is the 3rd condition used in the For Loop. It is used to change the value of the variable used in the EvalExpression. This expression is evaluated for each pass through the loop as well, but at the end of the workflow. This condition is optional.

Implementation of the for loop Container

Lets implement the For loop container. I will be using the project I created in the my article.

Step 1: Open the project in BIDS -> Add a Execute SQL task on the UI design surface. Insert the following script in the task

CREATE TABLE Forloop
( id INT IDENTITY(1,1) NOT NULL,
Mydate DATETIME
)

Step 2: Drag and drop a For loop container inside the design UI surface -> Drag and drop a Execute SQL Task inside the For Loop Container -> Insert the following code inside the Execute SQL Task

INSERT INTO Forloop VALUES (GETDATE())

Step 3: Click on the variable tab -> create a variable called counter with data type as Int64 or Int32.
IS44Step 4: Right Click inside the For loop container and click on edit. Fill in the expressions as depicted in the pic below -> Click on OK.
IS45Step 5: Press F5 and make sure the execution succeeds
IS46
What we observe in the database is that the table has been created with 10 rows inserted in the table.
IS47
In this is article I have tried to implement the For Loop container in a very simplified manner. I hope I was able to do that.

 

 

 

 

 

Advertisements

Tagged: , , , , , , , ,

One thought on “SQL Server Integration Services – For Loop Container

  1. […] my previous article I wrote about the For loop Container. In this article I will show how we can implement the Foreach […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: