Run job B only after job A succeeded today: table-driven SQL Agent job dependencies

#The usual approaches and their problems

#The table-driven design

CREATE TABLE dbo.JobDependency (
  job_name         sysname NOT NULL,
  depends_on_job   sysname NOT NULL,
  CONSTRAINT PK_JobDependency PRIMARY KEY (job_name, depends_on_job));

INSERT dbo.JobDependency VALUES
 (N'ETL - Transform Sales', N'ETL - Extract Sales'),
 (N'ETL - Transform Sales', N'ETL - Extract Products'),
 (N'ETL - Publish Mart',    N'ETL - Transform Sales');

Each dependent job gets a schedule and a first step that checks its predecessors.

#The gate procedure

CREATE OR ALTER PROCEDURE dbo.usp_JobGate
  @JobName sysname,
  @WaitMinutes int = 120,       -- how long to wait for predecessors
  @PollSeconds int = 60
AS
SET NOCOUNT ON;
DECLARE @today int = CONVERT(int, CONVERT(char(8), GETDATE(), 112)),
        @deadline datetime = DATEADD(MINUTE, @WaitMinutes, GETDATE()),
        @delay char(8) = CONVERT(char(8), DATEADD(SECOND, @PollSeconds, 0), 108),
        @missing nvarchar(max);

WHILE 1 = 1
BEGIN
  SELECT @missing = STRING_AGG(d.depends_on_job, ', ')
  FROM dbo.JobDependency d
  WHERE d.job_name = @JobName
    AND NOT EXISTS (
      SELECT 1 FROM msdb.dbo.sysjobhistory h
      JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
      WHERE j.name = d.depends_on_job AND h.step_id = 0
        AND h.run_status = 1 AND h.run_date = @today);

  IF @missing IS NULL RETURN;    -- all predecessors succeeded today

  IF GETDATE() > @deadline
  BEGIN
    DECLARE @msg nvarchar(2000) = CONCAT(N'Predecessors not complete: ', @missing);
    THROW 50010, @msg, 1;
  END
  WAITFOR DELAY @delay;
END

Step 1 of ETL - Transform Sales:

EXEC DBA.dbo.usp_JobGate @JobName = N'ETL - Transform Sales', @WaitMinutes = 180;

On failure: quit reporting failure. The notification tells you which predecessor didn't finish.

#Define "today"

"Succeeded today" by run_date is simple but breaks for batches that cross midnight. If your batch starts at 22:00, use a business date instead: have each job write a row to dbo.BatchRun (job_name, business_date, status) in its last step and check that table, not msdb history.

#See the whole chain

WITH last AS (
  SELECT j.name, h.run_status, h.run_date, h.run_time,
         ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY h.instance_id DESC) rn
  FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
  WHERE h.step_id = 0)
SELECT d.job_name, d.depends_on_job, l.run_status AS predecessor_status, l.run_date, l.run_time
FROM dbo.JobDependency d
LEFT JOIN last l ON l.name = d.depends_on_job AND l.rn = 1
ORDER BY d.job_name;

#Trade-offs

• A waiting gate step occupies an Agent worker thread and shows the job as "running". That's fine for a handful of jobs. Prefer short waits plus a later retry schedule if you have many.
• Rerunning a predecessor by hand counts as success for the day. Usually that's what you want.
• Cross-server dependencies: query the other server's history through a linked server, or have predecessors write to a shared BatchRun table.

Tip

When the chain grows beyond 10–15 jobs with branches, consider a real orchestrator (ADF, Airflow, SSIS master package). The table approach is for keeping small estates simple.

#Checklist

• Dependencies in a table, reviewed with job owners
• Gate step first in each dependent job
• Business date for cross-midnight batches
• Chain status query in your runbook