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
Open the package created in Part 1 and pull a For Loop container task into the control flow.
- Drag the script task created in part 1 into the For Loop container.
- Right click on the For Loop Container and click “edit”.
- Under For Loop properties you will see three options.
- InitExpression – This value is optional. Here you can assign an initial value. (More on this shortly)
- 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.
- 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.
- For InitExpression enter the following: @bolDoesFileExist = False.
- Notice I used one (=) sign here. In SSIS you use one = for assigning values.
- For EvalExpression enter the following: @bolDoesFileExist == False.
- 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.
- For Assign Expression, leave this value empty. Click ok to close out the For Loop Editor.
- 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.
- Choose OLEDB for connection type
- Select any OLEDB connection, doesn’t matter which one you choose.
- 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.