Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Make an SSIS package Delay or Wait for Data

Packages can be scheduled to run at a time when you expect data to be in a database. Instead of guessing the time when the data will be in the database we can have the package look for data in a SQL table. When the table has data then the package will begin.

First we will create two variables on the package, intDelayTime and intSQLCount, both are integers.Then we drag out a For Loop. Set the InitExpression to @intSQLCOunt = 0 and the EvalExpression to @intSQLCount == 0. This will cause the loop to run until the intSQLCount is not zero.

SSIS Delay Package Start

Drag and drop a Script Task inside the For Loop. Set the intDelayTime as a read only variable. Enter the following code.

Public Sub Main()

Dim sec As Double = Convert.ToDouble(Dts.Variables(“intDelayTime”).Value)

Dim ms As Int32 = Convert.ToInt32(sec * 1000)

System.Threading.Thread.Sleep(ms)

Dts.TaskResult = ScriptResults.Success

End Sub

This saves the time delay variable as an integer and changes it to milliseconds. This will cause the task to pause for the number of second saved in the variable.

SSIS Delay Package Start

Drag in an execute SQL task and connect the delay script task to it with a success constraint. The execute SQL task will look at a the table and do something like a Select Count (*). We will set the result set to single row and save the count in the intSQLCount variable.

Select Count(*) from WatchMe

SSIS Delay Package Start

This will save the row count in the variable and the For Loop will continue until this number is not  zero. Once it is not zero the loop will complete and the rest of the package can run.

SSIS Delay Package Start


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...