A question was recently asked on the SSIS forum about how you can put a package "to sleep". In other words, pause execution for a defined length of time.
It was suggested to use a script task to do this. That would definately work but there is actually a much easier way - use an empty For Loop. Here's a screenshot showing how to set it up.
As you can see, all you need to do is set the EvalExpression property. It really is that easy. The expression here:
DATEADD( "ss", 10, @[System::ContainerStartTime] ) > GETDATE()
will pause the package for ten seconds. It works by looping until the time that it started looping plus ten seconds is less than the current time. Very simple indeed.
One thing to be aware of in regards to this. In the current build of SSIS (SP1) there is a small bug that you should be aware of when using the For Loop or ForEach Loop. Each iteration of the For Loop or ForEach loop hangs onto a bit of memory - in other words there is a small memory leak. Hence if you loop enough times there is a miniscule possibility that you could get an out of memory exception. This bug has been fixed in SP2.
This bug information comes from Travis Maddox. who was running a process in an infinite loop. On each loop iteration he was processing a 100k file (approximate size). The steps outlined above for putting a package to sleep should not result in an infinite loop unless you code it up wrongly. The point being that it would take a huge processing requirement for you to encounter this bug so in all but the most extreme circumstances it really isn't anything to worry about when you are building a "sleep" For Loop that doesn't actually process any data..
My thanks go to Travis for this information.
UPDATE 2006-11-02: I neglected to check this out properly and I should make you aware that this technique may send your CPU utilization sailing a little close to the edge. Make sure you check out CPU util before you fully employ this technique.
Thanks to Darren Gosbell (and a couple of others) for pulling me up on this. And I apologise for not checking this out fully first. A lesson learnt methinks.