For Loop container example in SSIS with C# script

Looping is one of the powerful tools since very long in each programming language. It has no different concept in SSIS too. Here in SSIS, For Loop container defining repetitive control flow in a package.

Generally in looping, three steps are very important. 1.) Initialization of loop variable 2.) condition for exiting the loop 3.) Increment/Decrement. In SSIS too, you have to be familiar with three steps only in order to execute For Loop Container.

 Let us now see the small example.

Create new project in BIDS (Business Intelligence Developer Studio) for this example and add two variables in the variable window.

1.)    “Count” type of Int32 with value of 5

2.)    “Increment” type of Int32 with value of 0

Once you are done with adding variable, drag For Loop container from tool box and drop it to the work area.  To configure loop container, double click on that.

Now, set following properties in “For Loop Editor”

1.)    InitExpression to @Increment=1

2.)    EvalExpression to @Icrement<@Count

3.)    AssignExpression to @Increment=@Increment+1

See the second paragraph of this article where I have mentioned three important steps for looping in programming language, same kind of three steps with different name we are setting up in “For Loop” container here in SSIS.

For more details, look at below screen shot.

Actually we are going to show message box for each iteration of “For Loop” container. Our Increment variable is set to 1 and it will run until it becomes greater than count variable which is 5. Show for displaying the message we are going to use “Script Task”.

Once you setup “For Loop” container, drag “Script Task” from tool box and put it inside “For Loop” container, double click on “Script Task” to configure it. We are going to use “Increment” variable inside the script task to display which iteration is going on. For using Increment variable, we have to select that variable in Script task. So, in “ReadWriteVariables” property of script task should have “Increment” variable, you can select that variable by clicking on Ellipse button besides “ReadWriteVariables” property. Once you set it, click on “Edit Script” button to write down script.

For more details, look at image below:

Once, you will click on “Edit Script” button, you will find one script edition and find “Main” method, which is our entry point, in that script editor. Put below given code in Main method and close “Script Editor” and click “OK” button in “Script Editor” dialog box.

System.Windows.Forms.MessageBox.Show("Hi from SSIS, right now loop counter is at " +  Dts.Variables["Increment"].Value.ToString(), "information");

            Dts.TaskResult = (int)ScriptResults.Success;

Now, you are ready to run this simple application by hitting “F5”.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com