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.


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


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.

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.


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#

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


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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (62 votes cast)
VN:F [1.9.22_1171]
Rating: +44 (from 50 votes)
For Loop Container in SSIS, 9.2 out of 10 based on 62 ratings
  • Pingback: Containers in SSIS | pHpRing()

  • Hey dude. what kind of wordpress theme are you using? i want it to use on my blog too .

    • Chander Sharma

      Hi Buddy,
      I am using Sampression theme. What kind of blog you are running?

  • idrisha

    any person can now easily learn dis ……its rilli gud xplanation!!!

    • Chander Sharma

      Thanks alot Idrisha,
      SSIS is really a great tool and its fun to work on.

  • mitali

    The object name “insert Sr_No values into Db is not valid.The name cannot contain any of the following character:/\[].=

  • its great article bro very nice but i dnt have idea on .net.
    many people searching for for loop container example in web but nobody will write articles on this ,indeed me to searched for lot of times for for loop container,finally i learned this through pluralsight.
    am very glad to see once again article on for loop container.


  • munusamy

    please replace “Set the EvalExpression option to – @Sr_No < = 5." instead of this
    "Set the EvalExpression option to – @Sr_No =< 5."

    • Hi Munusamy,

      Thanks a lot for correcting me. 😉
      It’s been updated.

  • darurashok

    Hi Chander,

    Your Simple way of explanation is very good to understand quickly and in easy way…

    I appreciate your work.

    Thanks and Regards,
    Darur Ashok

  • Sumalatha

    This step by step process is very useful for me. Thanks for updating

  • Swati Patil

    Thank you for lucid explanation. anyone can understand concept easily by reading ur blogs.

  • shally choudhary

    Its really a good explanation…. it covers three component along with for loop container…:)

  • Harsha

    Hi ,
    I am getting only one record in the db and only one pop up if condition is <=3 i get the values 4 2015-06-06 00:00:00.000 in sr_no and date respectively.
    Why is it so?

  • Harsha

    oops sry !!! Just placed the script task and sql task outside the For loop container.. 🙂

    • Hi Harsha,

      We always try to avoid Big mistakes but got stuck with Small one.
      I appreciate you took out the time to Comment back and made us know why your SSIS package was not configured properly.

      Chander Sharma

  • Jansi


  • Rob

    Love it just have to get it to loop through a select list a temp table grabbing 2 variables