Controlling Job Sequences

Question

Post reply

Controlling Job Sequences

Vinu verma

Right there with Babe

Points: 795
More actions
December 18, 2007 at 4:50 pm

#119368

Hi,
I have 4 jobs and these jobs should be run one after the other. How do i do this. I was planning to use sp_start_job, so the process flow controllin job wud be like 5 steps each with the sp_start_job. But my issue is that i want the second job to run only after the successful completion of the first job. If i use the sp_start_job wouldnt the second task as soon as the first task is a success, because here success mean whether the job started to run or not. So any way to tackle this situation
Thanks
Vinu

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

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

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 1

you might consider a "master package" in which you set the controlflow for the packages to be executed.

In its simplest form: two execute package tasks, with a "success" constraint between the first and the second.

Peter Rijs
BI Consultant, The Netherlands

Vinu verma Right there with Babe Points: 795 More actions · Answer 2

These are 4 jobs and not 4 DTS packages. Each of the 4 jobs have a number of DTS packages inside them and they are pulled from different datasources.

Thanks

Vinu

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 3

OK, you could consider the following:

- use a simple table to hold the last successfull run of each job

- start each job by checking if the necessary jobs have run successfully since the starting job's last successfull run

We used a similar scenario for an ETL-process where we did a double staging:

Stage 1 was filled with a snapshot of the source table

In Stage 2 the history was maintained per table

Of course you don't want to build history on a stage2 table, when the corresponding Stage1 table is empty. Therefore we used a "succesfullrun" table where Stage 1 and Stage 2 packages wrote their successfull run per table. Stage 2 would check for each table if the corresponding stage 1 table had been refreshed since the stage 2 table was last updated.

BTW1: are you on SQL 2005 ('cause you speak of DTS packages)?

BTW2: you mention different datasources. I assume that's not why you have 4 jobs and several packages, do you?

Peter Rijs
BI Consultant, The Netherlands

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Make the first step of the each job (except the first one) run a query of msdb.dbo.sysjobhistory looking for job_id = the id of the job that is the precedent, run_date = current date, and run_status = 1. If the query indicates the previous job succeeded, go to the next step of the current job which would execute the DTS package.

Greg