I have a SQL job agent that has two job steps: PackageA and PackageB. PackageB follows PackageA but PackageB should never run between 06:00 and 18:00. Unfortunately, PackageA sometimes takes a long time causing PackageB to run between 6:00 and 18:00. In PackageB I have a constraint+expression that checks whether the time is between 06:00 and 18:00. If so, PackageB exits nicely and sends an email to an operative reminding him/her to reschedule PackageB. However, things would be very much better if I could have the package reschedule itself.
Logic in PackageB:
ExpressionTask --> fills variable @Hour with datepart("Hh",GETDATE())
If the found value is <6 or >18 the 'normal' procedure is executed or it starts an 'send email' and exits. What I would like is to add a command after sending the email that would schedule PackageB to run once that same evening at 19:00. Ideally the procedure would check whether there is a job for just running packageB and schedule that to run once that evening at 19:00 and if there is no existing job for running PackageB it should be created.
My feeling says this should be doable using TSQL. Any suggestions or are there better ways to do it besides using TSQL?
Thanx for thinking with me!
You can implement a T-SQL job step between PackageA and PackageB or a Execute SQL Task in SSIS that checks the time and uses the WAITFOR statement to wait till 19:00.
IF DATEPART(HOUR, SYSDATETIME()) >= 6 OR DATEPART(HOUR, SYSDATETIME()) < 18
BEGIN
WAITFOR TIME '19:00';
END;