How to use For Loop Container in SSIS

Description:-

The For Loop Container in SSIS will repeat the SQL Integration Services tasks for given number of times, until the given condition is False. This is same as the For Loop in any programming language. In this article we will show you the step by step approach to configure the For Loop in SSIS with example.

Here in this example we will export the data form the data base and store it into flat file destination. Here I have used Employees table to get data from the data base and used Flat file destination tools to store data. Below table I have used in the source control.

Drag and drop the Data flow task from the SSIS tool box double click on data flow and click edit.

Now drag and drop OLE DB Source and configure it with the data base server and used Employees table as source table. After check the columns tab to verify the table columns.

Now drag and drop the Flat file destination tolls from the SSIS toolbox and configure it with the destination file. Map the Input columns and output columns like below.

Now run the package and check the destination file to cross verify the data.

Now my purpose is I want to execute same data 3 times in the flat file destination. For that first I will create variables ‘Counter’ like below.

Now drag and drop the For Loop container in the data flow task like below.

Double click on it will open the For Loop Editor. You can use this editor to configure it. Please refer For Loop in C Programming to understand the programming flow of the For Loop in step by step manner. Even if you don’t know C language, it will help you understand the loop concept.

· InitExpression: For loop starts with the initialization statement so, initialization of counter variable should done first (For example @counter = 1 or @i = 1.).

· EvalExpression: The value of the counter variable is tested against the test condition. If the condition is True then it will execute the For loop container. If the condition fails then For loop will be terminated.

· AssignExpression: This expression is executed after the end of each iteration. It helps to increase or decrease the counter variable as per our requirement.

Under the General section, Please specify valid and more meaningful Name and Description.

Next, open the For Loop Editor, and assign the appropriate values. From the below you can see that, For Loop will start at 0, will executed until it reaches to 10, and for each iteration Counter value will be incremented by 1.