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.
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)
Dts.TaskResult = ScriptResults.Success
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.
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
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.