Home Forums Data Warehousing Integration Services SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for the Current Date RE: SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for the Current Date

  • Why let the package that checks for the record exit? Just let it run. This way you would only need on SSIS package.

    There are lots of options but here are two:

    1. Use an Execute SQL Task that calls a stored procedure that employs WAITFOR DELAY. The proc would loop waiting for 15 minutes if no record were found and would return if a record were found allowing the SSIS package to continue.

    2. Use a Script Task that calls a stored procedure to check for the record that returns a resultset letting your code know whether it should proceed. Have the .NET code loop over calling this proc using Thread.Sleep to wait 15 minutes in between attempts.

    The end result is that you are not having to start an SSIS package every 15 minutes, creating msdb job history activity and memory and CPU activity loading and unloading an SSIS package. You also avoid having to construct Control Flow logic in an Agent Job which avoids creating a dependency between your SSIS package and your job scheduler.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato