July 14, 2010 at 1:53 am
I have four SQL Agent jobs calling SSIS packages, all work fine, all permissions set ok.
I wanted to set them up to run sequentially so I created a 5th job with 4 steps, each step running one of the original 4 jobs. The first three were set up to 'Quit the job' on Failure, and 'Goto the next step' on success. The final step is set to 'Report success' on success and 'Quit the job' on failure. Logical or so I thought.
What I'm getting is all 4 steps starting at the same time. This causes one step to fail as it can't run at the same time as one of the other steps (I could code round it but that doesn't fix the issue). The failed step DOESN'T fail the main job even though it is (supposedly) set up to do so, it completes and reports as a success.
I am using SQL 2008 SP1 but I am not sure that is the issue in itself, I wouldn't be surprised if there's a switch somewhere that I am missing.
(The jobs run overnight so if I rerun the failed step/job the following morning, it runs and completes like a dream!)
Any ideas anybody?
July 14, 2010 at 7:38 am
The problem you are having is that the start job feature is asynchronous, in other words, all it does is start the job. It doesn't wait for it to finish to end the step and return success. To do what you want, each job in the process is going to have to have one last step, to fire off the next job in the chain. In order to fire off a second job after the completion of the first, you either need to have the last step of the first call the second or else update a table flag somewhere that the second job is looking for and uses as the cue to start.
July 14, 2010 at 8:04 am
Jeff, thanks for that, that is exactly the answer I needed.
I had suspected as much as I changed the main job (about an hour ago) to run the 4 SSIS packages directly instead of calling the 4 other jobs to do it. You and I both know it worked exactly as I wanted it to originally.
Thanks for taking the time to answer, most appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply