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

Advertisement
This entry was posted in SSIS, Syndication, T-SQL and tagged . Bookmark the permalink.

4 Responses to Make an SSIS package Delay or Wait for Data

  1. Philippe Pack says:

    or you can use SQL instruction WAITFOR …

  2. Mandy says:

    Thanks for the post I was looking for this

  3. LindAtWork says:

    Might be a question that should have had an obvious answer BUT – you would set the number of seconds you want as your delay in the intDelayTime Value, before making it ReadOnly ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s