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.

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime

Public 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 Sub

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

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.

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

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.