Job continue running while step failed

Question

Job continue running while step failed

moshehayun

SSC Enthusiast

Points: 127
More actions
October 21, 2013 at 8:04 am

#395695

Hello everyone,
I'm totally new in this forum, so I hope I'm on the right place
There's my problem:
I have a SQL Job that contains one step only(SSIS package). This job is automatically executed daily. Sometimes the step fails but the job continue running for days / months... I have to set the job rules so that it completely stops when the step fails, so it run again correctly on the next day.
I've already set "Quit the job reporting failure" on failure action for the step.
I just have no idea what i miss? Why the job's still running while the step failed? :angry: :angry: :angry:
Thanks in advance
Moshé

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sestell1 SSChampion Points: 10230 More actions · Answer 1

There are a couple of things that could be happening here. It is possible that the step being executed is in turn calling an external process, and the external process is not returning an exit code, leaving the job step waiting for a response. It is also possible that the job isn't actually continuing, but the history for the job is not getting updated.

What is the one job step actually doing, and what causes it to fail?

moshehayun SSC Enthusiast Points: 127 More actions · Answer 2

Hi,

The step is a data import through SSIS package. It have to get a file from a local storage and import the data in Dynamics CRM. But sometimes, the file is not yet available when the package start, so it throws an error. There's a screenshot:

In green, a job execution from July 27th which failed, but correctly stopped. The job on the next day started normally.

But (in red) the job execution from August 3rd which failed too (same error) but the job continued running until we stopped it manually on October 14th.

sestell1 SSChampion Points: 10230 More actions · Answer 3

I've seen a similar problem with invalid paths in SSIS just hanging instead of erroring out. We never did find a solution. Instead we ended up using a forEach loop to loop through files in a folder rather than using a fixed path and filename. That avoids the problem but makes error handling a bit trickier in your case.

moshehayun SSC Enthusiast Points: 127 More actions · Answer 4

Hello,

I already have a loop in my package. The file name is static, except the extension (.214) which correspond to the day number of the year.

What's really weird is that the job stops in the majority of cases for the same error, but once every 6 months, it doesn't.

I checked on the server's event viewer, but it turns out that the job is done correctly.

MS bug? a service pack that fixes it eventually?

Thanks for your time 🙂

Moshé