For Loop Container in SSIS
Description – Before implementing the For Loop Container, I would like to tell you that i like this container the most out of all the mentioned Containers. Let’s not waste much of our time and get started with the example on For Loop Container.
Example Scenario :-We will create a table with the name Data having fields Sr_No and Date. Then, with the help of For Loop Container, we will increment the values of Sr_No from 1 to 5 (iterative process) and insert them into our Data table.
STEPS TO FOLLOW :-
STEP 1. Open SQL Server Management Studio (SSMS) and Create a table named Data with fields as Sr_No and Date, in your working database (in my case it’s chander) using the simple script :-
Create Table dbo.Data ( Sr_No int not null, Date datetime not null )
STEP 2. Open Business Intelligence Development Studio (BIDS). Now, Create a package and edit its name to cK_For_Loop_Container.dtsx (You can have any desired name).
STEP 3. Add a New OLEDB Connection to the Connection Manager. Click New button and configure OLEDB Connection Mangaer pointing to your working database (as in this example, I am using chander as my database) and Click OK.
STEP 4. Open the cK_For_Loop_Container.dtsx package, Create a new Variable, and name it (say Sr_No).
- To open the Variables window, Right click in the design pane and select Variables or click on the Variables icon on the top right of your Package designer screen. Once the window is open, click the Add Variable button. Accept defaults for the Variable i.e Data Type – int32, Scope – Package level and a value to be 1.
STEP 5. Go to For Loop Container in the toolbox and Drag it to the Control Flow and Double click it to open the editor. It will have the following fields:-
(1). InitExpression :- This expression is Optional and evaluated once at the beginning only. It is used to initialize a variable that will be used in the For Loop Container.
- Set the InitExpression option to – @Sr_No= 1. This will initialize the loop by setting the Counter variable to 1.
(2). EvalExpression :- This expression is Required and also evaluated before any work is performed inside the container. This is the expression that determines if the loop continues or terminates. If the expression entered evaluates to TRUE, the loop executes again. If it evaluates to FALSE, the loop ends.
- Set the EvalExpression option to – @Sr_No <=5.
(3). AssignExpression :– This is the last expression used in For Loop and is optional. It changes the value of the variable used in the EvalExpression.
- Set it to – @Sr_No = @Sr_No + 1 for the AssignExpression and Click OK.
STEP 6. Drag a Script Task into the For Loop Container and double-click the script task to edit it. In the General tab, name the task Pop up the message.
STEP 7. In the Script tab, set the Read Only Variables to Sr_No. Finally, click Edit Script to open the Visual Studio designer. By typing Sr_No for that option, you’re going to pass in the variable parameter to be used by the Script Task.
STEP 8. When you click Edit Script, the Visual Studio design environment will open. Replace the Main () subroutine with the following code. This code will read the variable and pop up a message box that displays the value of the variable:-
- For C#
Public void Main ()
System.Windows.Forms.MessageBox.Show(“Sr_No = ” + Dts.Variables[“User::Sr_No”].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
- For VB
Public Sub Main ()
Dts.TaskResult = ScriptResults.Success
Save and exit the Visual Studio design environment, then Click OK to exit the Script Task.
STEP 9. Drag an ExecuteSQLtask into the For Loop container and double click the task to edit it.
- Set the Name as Insert Sr_No values into DB.
- Set the Description as Execute SQL Task.
- Set Connection Type as OLEDB and set Connection to the connection created in STEP 3.
- Set SQLSourceType as Direct Input and in the SQLStatement, put the following insert statement :- Insert Into dbo.Data Values (?,?)
STEP 10. Inside the Execute SQL Task editor, Go to Parameter mapping tab and create two parameters as shown in the figure.
STEP 11. Connect the success precedence constraint from Script task to the ExecuteSQLtask.
STEP 12. Execute the package, you should see message box popup with each new value of Sr_No variable. That is, you should see five pop-up boxes one at a time, starting at iteration 1 and proceeding through iteration 4. Only one pop-up will appear at any given point. After the loop is complete, the For Loop Container, the ScriptTask and the ExecuteSQL task will all be green. Check the rows in the Data table.
Select * from dbo.Data
This completes the implementation of For Loop Container. I hope you find this post interesting. Your comments are invited and I will be happy to solve your queries.