May 19, 2008 at 2:10 pm
I have 4 SQL Server agent jobs. Each executes it's own set of SSIS packages. Now I want the jobs 1 & 2 to run in parallel before job 3 and 4 runs..And it should be one single job that has the dependencies..I m not sure if I can establish this dependency with in another SQL Server agent job.
I also thought of an idea of putting all the 4 SQL Server agent jobs in a SSIS package and then kicking off the package by creating another job..
But it's becoming complex.
Can you please suggest me the best way of doing this ?
Eg - Job 1 - SSIS1,
SSIS2
Job 2 - SSIS3
SSIS4
SSIS5
Job 3 - SSIS6
SSIS7
Job 4 - SSIS8
Now I want a single job that executes job1 and job2 in parallel and after that job3 and job4..
May 19, 2008 at 6:36 pm
This may work.
Make one new job, call it Job 0. When Job 0 runs all it does is add a new schedule or update existing ones to Jobs 1, 2, and 3 to start running in, say, 1 minute from now. [sp_add_schedule or sp_update_jobschedule.] That way Jobs1, 2, 3 all start work independently.
Jobs 1 & 2 run their SSIS packages in step 1. In step 2, which runs on success of step 1, run something like 'update MyJobList set Job1 = 1' and 'update MyJobList set Job2 = 1', respectively.
Job3 starts an infinite loop (you may want to have a failsafe cut off after N minutes) while MyJobList.Job1 = 0 and MyJobList.Job2 = 0. When the while condition is broken by Jobs1 and 2 finishing, reset the flags to prepare for the next run--update MyJobList set Job1 = 0 and update MyJobList set Job2 = 0--then go to the SSIS package step.
Job4 fits in a similar way.
You should verify none of the jobs are already running before you tell them to go.
May 19, 2008 at 10:24 pm
I think that you are pushing the limits of what you can easily do with SQL Server Agent. Either use another scheduling engine that knows about these sort of dependencies or create a SSIS package that is responsible for running your current set of SSIS packages with the dependencies you need. Using SSIS to do this is definitely easier than SQL Agent since it already has precedence constraints etc that are an exact match for your requirements.
July 16, 2008 at 1:14 am
Can u plz write down the steps. As I m unable to find the type to run the job inside a job. I m using the type as SQL Server integration System Packages but there in it there is no option to select a job....
Plz tell me the steps to select a job
March 14, 2011 at 11:45 am
I have a similar issue and was contemplating creating a "driver" SSIS Package to kick off the other pkgs. One question I had was that with Agent you can get job information from the MSDB system tables in regards to running time, next run, etc... If I use a "driver" SSIS pkg will I still be able to get that information? If so, where? Thank you in advance for your help.
March 15, 2011 at 2:00 am
I'm using
exec msdb..sp_help_job
to see what jobs are running, can have a look at it as well
March 16, 2011 at 4:24 pm
Create a SSIS package in Bids
In control flow area, drag and drop two Execute SQL tasks in parallel and configure them as
Execute SQL task1 --- sp_startjob 'JOBNAME1'
Execute SQL task2 --- sp_startjob 'JOBNAME2'
Then drag and drop two more Execute SQL tasks in parallel and put them in a sequence container and configure them as
Execute SQL task3 --- sp_startjob 'JOBNAME3'
Execute SQL task4 --- sp_startjob 'JOBNAME4'
Drag and drop the green precedence constraints (On Success) from Execute SQL tasks 1 & 2 onto the sequence container and specify an AND condition by selecting both the precedence containers and select AND.
In this way JOB 1 and 2 executes parallely and once both of them succeeds then only Jobs 3 and 4 will be executed parallelly.
Thank You,
Best Regards,
SQLBuddy
March 17, 2011 at 4:42 am
Using Execute SQL tasks to run sp_start_job will not actually achieve that. sp_start_job will start a job and then finish - it does NOT wait for the job to finish. The job could run for hours. The SSIS package just described would (probably) run for less than a second and all 4 jobs will probably be executing at the same time.
You will need to add code to wait for each job to finish (and you probably should check whether the job is currently running before attempting to start it. Otherwise, sp_start_job will report an error).
Check out the code in sp_help_job - it has code that checks whether the job is running.
June 23, 2011 at 10:23 am
Hi guys,
How about using SSIS - Sequence Container ?
-JP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply