Simplest example of For Loop in SSIS

Simplest example of For Loop in SSIS

Here I am posting a very simplest example of For Loop in SSIS . This example will assist SSIS freshers to understand the iteration technique of for loop.

For loop has only three properties need to set to iterate. These properties are:

  InitExpression: specify the Variable to initialise, this statement reads only once

  EvalExpression: specify the condition to check either iterate or not basis of true or false.

  AssignExpression: Increment the variable after every iteration

In this example we are first checking the table is created or not, if not then create a table. Once table created insert a value into that table repeatedly using for loop.

Step 1:    To begin with For loop we need to create a variable first, this is very simple just click a Add variable button in Variables windows and create a variable “i” of DataType int. if variable window is not present simply go to SSIS Menu and click Variables.

Step 2:    Once a integer variable create, Pick two Execute SQL Tasks and one For  loop Container from Toolbox on to Control Flow Tab and arrange as given in picture below.

Step 3:  After arranging the task and container, open the SQL Task Editor by double clicking Execute SQL Task, placed on the top of the For Loop Container.

Step 4:  Here Select a OLE DB Connection and write "create table.." statement in SQLStatement box. Exact statement is as follows

         IF NOT EXISTS (SELECT * FROM sys.objects
                        WHERE object_id = OBJECT_ID(N'TEST')
                        AND type='U')
          CREATE TABLE Test(ID INT)

Step 5:  Now Open For Loop Container Editor and set the properties for iteration ,in my case variable “i” is initialized with 0 and loop till 100 with increment 1 on every loop.

Step 6:   After setting up For Loop Property , double click on Execute SQL Task 1 placed under For Loop Container and open the editor.

Step 7:  Here  again select the same OLE DB Connection property and type simple “Insert into “ statement. After setting up all the properties of Tasks and Container and variables , simply execute this package. 

This package will iterate till 100 times and insert value into tableone every iteration. Go to database and check Table.

No comments:

Post a Comment