December 18, 2007 at 4:50 pm
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
December 19, 2007 at 4:33 am
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
December 19, 2007 at 9:06 am
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
December 19, 2007 at 10:18 am
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
December 19, 2007 at 2:54 pm
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
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy