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')
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.