In this post I am going to explain how to set up and use the For Loop container inside SSIS in a real world scenario. In a previous post I showed how to set up a script task inside SSIS to check if a File Exist. Well if the file doesn’t exists I want to keep checking over and over again until that file does exists. The For Loop container is just what I need. Let’s get started.

This post assumes you are building on my previous post, found here: Does File Exist

Walkthrough

Open the package created in Part 1 and pull a For Loop container task into the control flow.

1. Drag the script task created in part 1 into the For Loop container.
2. Right click on the For Loop Container and click “edit”.
3. Under For Loop properties you will see three options.
1. InitExpression – This value is optional. Here you can assign an initial value. (More on this shortly)
2. EvalExpression – This value is required. As long as this expression evaluates to true the loop will continue to loop. Once it evaluates to false the loop will stop.
3. AssignExpression – This value is optional. This value is used to change the value of the variable that you use in the EvalExpression. For example you can add 1 to each iteration of the loop.
4. For InitExpression enter the following: @bolDoesFileExist = False.
1. Notice I used one (=) sign here. In SSIS you use one = for assigning values.
5. For EvalExpression enter the following: @bolDoesFileExist == False.
1. As long as this expressions evaluates to true the loop will continue. Once the variable no longer is false (meaning that the file does exists) the loop will stop.
6. For Assign Expression, leave this value empty. Click ok to close out the For Loop Editor.
7. 
8. That’s it!

You might want to also add a delay between loops, maybe 5 seconds maybe 5 minutes, completely up to you. But here is an easy way you can add that functionality.

Pull an Execute SQL Task into your For Loop container.

1. Choose OLEDB for connection type
2. Select any OLEDB connection, doesn’t matter which one you choose.
3. Select Direct Input for SQLSourceType and type the following code for your delay statement:
• WAITFOR DELAY ‘00:00:05’

Here is how the final package will look:

Thanks for looking! Enjoy.