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).
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.
Public Class ScriptMain
Public Sub Main()
System.Windows.Forms.MessageBox.Show("For Loop Timer Iteration", _
Dts.TaskResult = Dts.Results.Success
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.
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.