SSIS - Uses with For Loop Container

How to configure a simple For Loop Container? The For Loop Container is a repeating container that does something untill a condition evaluates true.

Four examples:
- Do something ten times
- Wait untill a certain time
- Wait for a couple of seconds / minutes


A) Do something ten times
This example does ten times what's within the loop.

1) Variable
Create a integer variable named Counter. This variable is used the count till 10. 

Variable Counter (right click in Control Flow)

2) Add For Loop Container
Drag a For Loop Container to your Control Flow and give it a suitable name.

 

3) Edit For Loop Container

·                     Edit the For Loop Container and set the InetExpression to: "@[User::Counter] = 0". This is the initial value of the counter. This example starts at zero.

·                     Set the required EvalExpression to: "@[User::Counter] < 10". This is the evaluation expression that contains the expression used to test whether the loop should stop or continue. The example stops after 10 times (0, 1, 2, 3, 4, 5, 6, 7, 8, 9).

·                     Set the requires AssignExpression to: "@[User::Counter] = @[User::Counter] + 1". This is an optional iteration expression that increments the loop counter.

Set the expressions

4) Testing
For testing purposes I added a Script Task with a MessageBox that shows de value of the counter. 

Testing the For Loop

B) Wait untill a certain time
This example uses the For Loop as a Wait statement. Let's wait untill 12:20.

1) For Loop Container
Add a For Loop Container to your Control Flow and give it a suitable name. I added a Annotation to clarify that it does nothing within the loop.

Waiting For Loop

2) Edit For Loop Container
Only enter this EvalExpression: GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

EvalExpression

Clarification of the expression
It removes the time from the current datetime. So 01-02-2011 12:01:45.002 becomes 01-02-2011 0:00:00.000:
(DT_DBDATE)GETDATE()
After that it adds 20 minutes and 12 hours. So it becomes 01-02-2011 12:20:00.000:
DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))
That datetime is compared to the current datetime:
GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

3) Testing
For testing purposes I added a Script Task after the loop that shows the current time in a messagebox.

Testing

C)  Wait for a couple of seconds / minutes
With a slightly different approach then example B, you can wait for a couple of seconds, minutes, etc.
Use this expression in the same way as example B to wait 30 seconds:
DATEADD("ss", 30, @[System::ContainerStartTime]) > GETDATE()
or 5 minutes:
DATEADD("mi", 5, @[System::ContainerStartTime]) > GETDATE()

Click here for more information about DATEADD.

Note: Delay techniques with a For Loop causes unnecessary CPU processing. A Script Task with a Thread.Sleepdoesn't have that drawback.