SSIS – Wait/Delay in For Loop Container

Hi

In this post I would like to quick share a technique which has often come handy in my day-to-day development work.

At times when building SSIS package, I have to connect to S/FTP server to download/upload files and the server rejects the connection being busy, or if I have to loop and wait for an operation in other systems where I don’t have an option besides polling for operation completion status.

The following trick comes in handy:

Usually a plain loop itself in the SSIS is very fast:

With the run time information as follows:

Now for polling, I would like to introduce a wait/delay in each loop iteration with a 5 second delay, so for which I commonly use a “Script Task (C#)” which allows me to delay the iteration of (for loop) and can be placed at the end of operations in each iteration.

With the script content as:

(Here in the script the argument to the function is in milliseconds, hence 5 second delay is multiplied by 1000)

Leading to run time of our package now:

With the above trick we have now delayed the iterations of our for loop component, this C# script task can also receive delay value as a variable/parameter of the SSIS package which would allow to configure the delay amount without editing the Script component code.

Hope the above help.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*