Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running a SQL Server Agent job with in another job Expand / Collapse
Author
Message
Posted Monday, May 19, 2008 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2008 8:34 PM
Points: 16, Visits: 71
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..
Post #503126
Posted Monday, May 19, 2008 6:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 9:38 PM
Points: 144, Visits: 447
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.



Post #503249
Posted Monday, May 19, 2008 10:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:55 PM
Points: 2,950, Visits: 2,552
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.


Post #503288
Posted Wednesday, July 16, 2008 1:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2008 11:33 PM
Points: 7, Visits: 151
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
Post #534931
Posted Monday, March 14, 2011 11:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:34 AM
Points: 1, Visits: 26
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.
Post #1077897
Posted Tuesday, March 15, 2011 2:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 201, Visits: 681
I'm using
exec msdb..sp_help_job
to see what jobs are running, can have a look at it as well
Post #1078172
Posted Wednesday, March 16, 2011 4:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 1,194, Visits: 2,215
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
Post #1079375
Posted Thursday, March 17, 2011 4:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:55 PM
Points: 2,950, Visits: 2,552
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.



Post #1079551
Posted Thursday, June 23, 2011 10:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 15, 2014 10:06 AM
Points: 385, Visits: 732
Hi guys,

How about using SSIS - Sequence Container ?

-JP



Post #1130634
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse