SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running a SQL Server Agent job with in another job


Running a SQL Server Agent job with in another job

Author
Message
Sanju-518612
Sanju-518612
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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..
ksullivan
ksullivan
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 450
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.



happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4773 Visits: 3222
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.



parwinder_singh182
parwinder_singh182
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
rsehr
rsehr
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 62
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.
chris.stuart
chris.stuart
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 Visits: 796
I'm using
exec msdb..sp_help_job
to see what jobs are running, can have a look at it as well
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 2243
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
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4773 Visits: 3222
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.



Jp
Jp
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 775
Hi guys,

How about using SSIS - Sequence Container ?

-JP



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search