Starting a SQL Agent job from an SSIS package and waiting until it finishes

#The problem

EXEC msdb.dbo.sp_start_job @job_name = N'ETL - Refresh Cube';

That only requests the start. The SSIS package (or the calling job) moves on straight away and doesn't know whether the job ran, failed or is still running.

#A start-and-wait procedure

Create it in a utility database:

CREATE OR ALTER PROCEDURE dbo.usp_StartJobAndWait
  @JobName sysname,
  @TimeoutMinutes int = 120,
  @PollSeconds int = 15
AS
SET NOCOUNT ON;
DECLARE @job_id uniqueidentifier = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName);
IF @job_id IS NULL THROW 50001, 'Job not found', 1;

-- Refuse if it is already running
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity
           WHERE job_id = @job_id AND session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
             AND start_execution_date IS NOT NULL AND stop_execution_date IS NULL)
  THROW 50002, 'Job is already running', 1;

DECLARE @requested datetime = GETDATE();
EXEC msdb.dbo.sp_start_job @job_id = @job_id;

DECLARE @deadline datetime = DATEADD(MINUTE, @TimeoutMinutes, @requested),
        @delay char(8) = CONVERT(char(8), DATEADD(SECOND, @PollSeconds, 0), 108),
        @stopped datetime;

WHILE 1 = 1
BEGIN
  WAITFOR DELAY @delay;
  SELECT @stopped = stop_execution_date
  FROM msdb.dbo.sysjobactivity
  WHERE job_id = @job_id AND session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
    AND start_execution_date >= DATEADD(SECOND, -5, @requested);
  IF @stopped IS NOT NULL BREAK;
  IF GETDATE() > @deadline THROW 50003, 'Timed out waiting for job', 1;
END

DECLARE @status int = (
  SELECT TOP (1) run_status FROM msdb.dbo.sysjobhistory
  WHERE job_id = @job_id AND step_id = 0 ORDER BY instance_id DESC);

IF @status <> 1
  THROW 50004, 'Job finished but did not succeed - check job history', 1;

The procedure succeeds only if the job succeeded, and throws otherwise. The caller can handle it like any other error.

#In SSIS

Add an Execute SQL Task:

• Connection: OLE DB or ADO.NET to the server that hosts the job.
• SQLStatement: EXEC DBA.dbo.usp_StartJobAndWait @JobName = ?, @TimeoutMinutes = 90; with a package parameter mapped to the job name.
• TimeOut property of the task: 0 (no limit) or longer than @TimeoutMinutes. The connection's command timeout must not cut the wait short either.

If the procedure throws, the task fails and the package's normal error handling applies.

#Permissions

The account running the package needs to start the job and read activity/history:

Alternative: sign the procedure with a certificate so callers get only this one capability, without broad msdb roles.

#Variations

• Remote job: run the procedure on the other server through a linked server, or point the Execute SQL Task's connection at that server.
• From PowerShell: dbatools Start-DbaAgentJob -Wait does the same.
• Parallel jobs: start several with sp_start_job and poll them together, rather than calling the procedure one by one.

Warning

Polling holds a connection and a worker thread on the server for the whole wait. Fine for a few callers; don't build hundreds of concurrent waits this way.

#Checklist

• Procedure deployed with permissions sorted
• Task timeout and connection timeout longer than job runtime
• Failure of the job fails the package
• Already-running case handled