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 :-

Database

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

start

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.

oledb

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.

variable

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.

for loop_1

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.

genera_tab

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.

script_tab

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#

  • For VB

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 (?,?)

execute_sql

STEP 10. Inside the Execute SQL Task editor, Go to Parameter mapping tab and create two parameters as shown in the figure.

parameter

STEP 11. Connect the success precedence constraint from Script task to the ExecuteSQLtask.

final

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

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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.3/10 (37 votes cast)
VN:F [1.9.22_1171]
Rating: +34 (from 36 votes)
For Loop Container in SSIS, 9.3 out of 10 based on 37 ratings

Comments

  1. Reply

    • By Chander Sharma

      Reply

  2. By idrisha

    Reply

    • By Chander Sharma

      Reply

  3. By mitali

    Reply

  4. By avinash reddy munnangi

    Reply

  5. By munusamy

    Reply

    • Reply

  6. By darurashok

    Reply

  7. By Sumalatha

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Current ye@r *