SQL Agent Job - Set TimeOut and Force Failure to Notify

Question

Hi,

I moved a problematic ssis pkg that did not fail and ran for hours, normally 10min to its own Job

How can i force the job to end/fail when limit has expired?

Regards

Answer 1

You should probably extend your post to include the sql-server- integration-services tag, since the SSIS people may know if SSIS has any support for this.

I don't know SSIS, so I can only answer from a general Agent perspective. Agent has no support for this itself. You would have to set up a job that monitors the SSIS job step and kills it if has been running to long. This job could be started from an initial job step of the SSIS job. That job would wait for ten minutes and then kill the other job. Alternatively, you could schedule a job that runs every ten minutes or so and checks for the other job.

And, no, I don't have the full details to do this, since I don't work with SSIS.

But as you can see it is a bit of a kludge, and I am not sure that it is a good idea in the first place. What would be the benefit to kill the job?

Answer 2

Hi Ismail,

in that case I'd go for the suggestion by Erland. Start a separate Job/Process which either kills your SSIS Job after 10 Minutes or "just" monitors the SSIS Job.
If the job is still active and has a runtime > 10 mins, then kill the job and maybe restart it after 30 seconds.

But this is just a workaround. It is more important to figure out why the SSIS package stucks and cannot complete its task.

Regards
Dirk

Answer 3

Hi,
Thank you so much for assisting, i have an initial post on ssis forum where it was to fail the package but no success hence why i started the ques here and moved to its own job. i have expanded on the ssis with the same ques.

The reason being, is that when it does not fail it runs for hours and affects other jobs depneding on the data of the job. i cant stop and rerun later as im not aware of the issue/no alert for no failure

i have set the job to run twice in case it fails but if it hangs i can only stop it the next day manually

regards

Answer 4

Hi,
Thank You both, the pkg lookp and connects to oracle db's and it either tries to reconnect when unable to connect via c# script ot lost connection it just hangs.
Regards

Answer 5

I will try and find an example of checking job then failing after10min