SSIS Wait and Sleep

SSIS doesn't have a built in wait task, which has lead to many different innovative solutions that have come out of this. Here's is yet on more which involves exploiting the script task, and threading class. The solution is fairly strait forward; it simply puts the thread to sleep for a given amount of time in milliseconds. Where you need a wait put a script task into the package. Inside this task place the following line of code.

System.Threading.Thread.Sleep(x)

 

The "x" represents time in Milliseconds. A code snippet of how this would look something like this.

Public
Sub Main()

Dim wait As
Integer

wait = CInt(Dts.Variables("IntervalWaitInMS").Value.ToString)

 

 

System.Threading.Thread.Sleep(wait)

 

 

Dts.TaskResult = Dts.Results.Success

End
Sub

 

Some helpful advice here is that it uses variable above IntervalWaitInMS to assign this value, which would usually come from a user entered config. The problem here is this is in milliseconds, and this number can become quite large. If for example you wanted to have wait period of 1 hr, you'd have to enter a value of 3600000. Missing one 0 in your config for a value 360000 is the difference between 1 hr and six minutes. To make it a bit more user friendly when setting up your configs you might want have the users enter their value in minutes, and do the conversion within the script, such as below

 

Public
Sub Main()

Dim wait As
Integer

'Convert Min to Milliseconds

wait = CInt(Dts.Variables("IntervalWaitInMin").Value.ToString) * 60000

 

 

System.Threading.Thread.Sleep(wait)

'MsgBox(Dts.Variables("ValidationStatus").Value.ToString)

 

Dts.TaskResult = Dts.Results.Success

End
Sub