For relational data-warehouses where you have the data load scheduled during off hours, timing of different steps in a sql job plays a vital role to decide whether to carry on with the load or postpone it for the evening.

We as a team recently encountered a scenario where we had to disable the execution of a ssis package (which ran through a sql job) because the current time crossed the threshold time. Now there are various ways to indirectly disable or bypass a job step, however all of these require admin's intervention. We needed an automated process wherein the package/load should not execute if the time limit is crossed.

Solution - One of my manager came up with a logic which helped us tackle this situation. Here is how the package looked like:-

Execute SQL Task that you see here controls whether the rest of the package should execute or not. Here is the script we used inside :-

Select current time >=  thresholdtime
then 1
else 0 end as intTimeCheckover

This snippet will simply compare the current time with the threshold time and would return the result as either 0 or 1. This result is then mapped to a variable of int type which is then used in the precedence constraint.
If the returned value is 0 then we would continue with the execution of rest of the package and if not then subsequent steps will be bypassed.

As it is obvious this alternative would only work in case of ssis packages, however we could on similar lines implement it for non ssis scenarios.

Regards
Lokesh
www.letsbemuscular.com