For Loop Container Samples

by Darren Green 10 Aug 2004 14:00

One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).

Timer Loop

This example shows how to create a simple timer loop, such that all tasks inside the loop container will be executed repeatedly until the timer value has expired.

To begin with we add a new variable named TimerLoop, of type DateTime. The variables window can be found through the View - Other Windows menu items or may be collapsed at the bottom of the designer workspace by default.

Next add a For Loop Container to your package. Now add the tasks to run inside the loop. For simplicity this example just uses a Script task, which for demonstration purposes I just added a message box just so you can see the task being executed.

Package Layout
Imports SystemImports System.DataImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain	Public Sub Main()        System.Windows.Forms.MessageBox.Show("For Loop Timer Iteration", _            "ForLoopTimer", _            Windows.Forms.MessageBoxButtons.OK, _            Windows.Forms.MessageBoxIcon.Information)		Dts.TaskResult = Dts.Results.Success	End SubEnd Class

Finally we will configure the For Loop Container itself. There are three key properties we can use, the first of which is an initialization expression (InitExpression). In this case we will initialize our variable, @TimerLoop, to a time ten seconds in the future.

The next property is the evaluation expression (EvalExpression). This is the loop expression that you would expect to form part of any loop construct, the test that is evaluated for every iteration of the loop, and whilst it is true, the loop will continue. As mentioned above the description within the UI is incorrect in beta 2. Our test is to compare the current time against our future time, @TimerLoop as set in the initialization expression.

The final property is the assignment expression (AssignExpression). This is an expression that is executed for every iteration of the loop, equivalent to adding an extra process to run inside the loop, but cleanly encapsulated as part of the For Loop Container. For this example none is needed, but it is illustrated in the Counter example below.

Container Properties

So as you can see from the screenshot above our initialization expression sets @TimerLoop to now plus 10 seconds. The test means the container will execute repeatedly for 10 seconds.

Counter Loop

This example shows how to create a simple counter based loop, such that all tasks inside the loop container will be executed a defined number of times. To help control the loop we will use two variables, firstly one to maintain the iteration count which will be incremented each time (@CounterLoop), and one that holds our iteration limit (@CounterLoopLimit).

To begin add the two variables, @CounterLoop and @CounterLoopLimit, both of type Int32. The value for @CounterLoop can be anything for now, but @CounterLoopLimit should be the number of times you wish this loop to execute.

Next add a For Loop Container to your package, then add the tasks to run inside the loop. For this sample we use a Script Task with a simple message box as above.

Package Layout

Now we can set the three controlling properties for our loop, as shown below.

Container Properties

Out initialization expression (InitExpression) ensures that we are starting cleanly by setting our counter to 0 (@CounterLoop). For each iteration the evaluation expression (EvalExpression) checks to see if we have reached our maximum iteration count as set above when we defined @CounterLoopLimit. You can also see assignment expression (AssignExpression) in use, which we use here to increment the counter by one for each iteration of the loop, otherwise the loop would never finish.

Comments (16) -

12/16/2008 6:24:19 AM #

susheel

hai can i use any of my packages in ssis
i have created employee table in my database
i want to use in forloop container
how can i use it
even i am declaring eno as variable
and even i am entering the loop values also
but i am not able to get the out put what i wanted
so can any one help
there r some examples on for lopp container
but i cant understand them
so plz help me in this regard

susheel India

12/16/2008 10:24:55 AM #

Allan Mitchell

Susheel.

It sounds like you want the ForEach Loop not the For Loop.

www.sqlis.com/post/Shredding-a-Recordset.aspx

Allan Mitchell Germany

1/11/2009 2:33:27 PM #

pssheba

I have 300 excel file in a folder. I want to export all of it unto one OLE DB table. I have to defins "source connection" but there is no source connection to a folder, onlt to a separte file. Other words: no way i could do it using the "Foe Each container".
Any suggestion ?

pssheba

1/19/2009 7:25:10 AM #

Divya

U have to use For Each Loop

Divya

1/21/2009 9:20:10 AM #

mano

To check the number of files, if the count of files are equal to 5 then only i should go to for each loop.how could i solve this in ssis?

mano India

3/18/2009 6:09:34 AM #

mariodavidantony

write a 'for' loop that displays numbers 16,18,20,....30?

mariodavidantony India

3/19/2009 9:26:51 PM #

Mark

Need a For Loop example dealing with file enumeration to access via dataconnection.

Mark United States

4/22/2009 4:00:10 PM #

Binoj

I have a req. where i need to loop through all the .sql files in a particular order and execute them.
So far iam successful in looping through a folder and execute all .sql files but how can i execute them one by one in a preferred order?

Any ideas? I was thinking to remane files with some numeric prefixes for eg; 1_filename, 2_filename

Now the issue is how do i loop in this order in SSIS? Im using a for loop container.

Binoj United States

7/28/2009 7:14:18 AM #

amar

Could tell me how to add those two variables @counterloop and @counterlooplimit.


Thank's in advance

amar India

7/29/2009 3:03:57 PM #

Allan Mitchell

You can add these variables in the usual way.  There is nothing special about them

msdn.microsoft.com/en-us/library/ms141670.aspx

Allan Mitchell Germany

1/27/2010 1:02:08 PM #

Xavier GS

Very useful post, thank you very much to everybody

Xavier GS Spain

2/26/2010 2:32:11 PM #

Alfredo A Gutierrez

This is an excellent example accurate, clear, and concise. Not even Wrox's SSIS Professional and Expert books or the SQL Server Unleashed series or Codeplex.com had an example like this. Thank You!

Alfredo A Gutierrez United States

3/19/2010 11:34:02 PM #

sEAN

Is it possible to initialize multiple variables in the InitExpression?

sEAN United States

4/5/2010 6:17:37 AM #

Steven Taub

Hy Allan.
Your example here is terrific.  I tried to add additional tasks to the loop, like a branch out of the loop onto another execute SQl Task, but it just stopped before going further. How do i get it to go to the next task outside of the loop, while I have the loop connected to the next task? It just stops cold.  Completes the loop without going further.

Steven Taub United States

4/8/2010 9:00:18 AM #

Darren

Sean, You can only initialise (or assign) one variable, the syntax parser refuses to accept anything else.

Steve, There is no real way to branch out of the loop, you can ensure the evaluation condition is exceeded to halt the loop, but task are either inside or out. Anything outside the loop can be made to execute after the loop by using workflow constraints. Select the loop to get the focus and green arrow to drag to a following task. If no constrain is used to relate the loop to other tasks, it may execute before, after or in parallel, according to the somewhat pseudo-random rules of the execution engine

Darren United Kingdom

8/30/2010 11:49:19 PM #

MSK

Awesome example with excellent explanation ... Keep on helping the newbies

MSK India

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS