SQL Server 2012 job scheduling

Question

I have a situation where I have 1 package (Package A) that take about 3 min to complete and another package (Package B) that takes about 4 min to complete. I need package A to run every 5 min via a job and package B to run every 15 min. However package B can only run after package A has been completed.

Example

5- Package A runs

10- Package A runs

15- Package A runs and then Package B

20- Package A runs

25- Package A runs

30- Package A runs and then Package B

Is there any way to schedule jobs to meet the requirements above?

Really appreciate your help. Thanks in advance!

Schedule the first job as Package A to Run at 5/10/20/25/35/40/50/55 and Schedule a second job with two steps: package a and package b to run at 0/15/30/45 and force it to only move to package b/step 2 once package a/step 1 succeeds. — dfundako, Dec 08 '16 at 22:01
Or modify package `A` to run package `B` as it's last step when appropriate. — HABO, Dec 09 '16 at 02:32
`A` starts at 02:15 and runs for 3 minutes. `B` starts immediately after `A` finishes at 02:18. `A` starts again at 02:20 while `B` is still running, but `B` is happy to run until 02:22 alongside `A`. Right? — HABO, Dec 10 '16 at 02:24

score 0 · Accepted Answer · answered Dec 08 '16 at 22:17

0

for package B i would just put in logic to check if package A has stopped running

the wait script would be

WHILE EXISTS (
SELECT
    *
FROM 
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON 
    job.job_id = activity.job_id
JOIN
    msdb.dbo.syssessions sess
ON
    sess.session_id = activity.session_id
JOIN
(
    SELECT
        MAX( agent_start_date ) AS max_agent_start_date
    FROM
        msdb.dbo.syssessions
) sess_max
ON
    sess.agent_start_date = sess_max.max_agent_start_date
WHERE 
    run_requested_date IS NOT NULL AND stop_execution_date IS NULL
    AND job.name = 'package A') 
BEGIN 
WAITFOR DELAY '00:00:30'; 
END

This would check every 30 seconds if package A has stopped running.

Alternatively, you create a table that would keep track of job status and have A write to it to keep track of the status.

answered Dec 08 '16 at 22:17

crowley

100
1
5

Any idea on what the script for a table to track the status would look like? – Mutai Dec 09 '16 at 01:31
yep create table dbo.tracking (job_status, job_title,job_id, – crowley Dec 09 '16 at 19:58
`create table dbo.tracking ( job_id IDENTITY(1,1) ,job_title varchar(30) ,job_status bit ,job_time_start datetime ,job_time_end datetime ) --at the start of your Job a you would set the status to 1 and at the end set it to 0 -- at the start of your job b you would do the following WHILE EXISTS ( SELECT * FROM tracking WHERE job_id = 1 and job_status = 1 ) BEGIN WAITFOR DELAY '00:00:30'; END ` – crowley Dec 09 '16 at 20:06
Thanks a lot. Appreciate it – Mutai Dec 10 '16 at 16:26

SQL Server 2012 job scheduling

1 Answers1