Blog Post

Terminate a SSIS package after specific time interval.



There are several ways you can execute scheduled SSIS package/job they are:


A)     SQL Server Agent Job.
B)       Windows Schedule Task.
C)      Third party program, like SQL Sentry


Nirav's Diary has example on using A and B to run SSIS packages here:


But if you want the execution to stop after x time has passed, then it's not so easy. Why would you like to do this in the first way? Well, example if you for have a specific maintenance window to run batches in, you don't want the cleanup job to run after the maintenance window is over. Let's check out what our options are today for the most common alternatives, A and B.

A)     If you are using Windows Task Scheduler for your SSIS jobs this is a simple task

In Windows Task Scheduler, if you go to the "Settings" tab of your task, there is a checkbox with the option to "Stop the task if it runs longer than:".

Set this to the desired time interval, and Task Scheduler will kill the process when the specified time is elapsed.


A)    If you are using SQL Server Agent job for your SSIS jobs is it not so easy. SQL Server Agent does not have a "stop task if it runs longer than" property(see sp_add_schedule


On solution around this problem is to create a "WatchDogTimer" job responsible for starting and monitoring the run time for another job. You put the start schedule on the WatchDogTimer job and remove it from the normal job.

The WatchDogTimer job only has a T-SQL job step with T-SQL code in it.


The T-SQL needed for WatchDogTimer job is below, replace the value of @JobToRun with your own value for the SQL Server Agent job to start and watch over. Don’t forget to also replace @TimeoutMinutes parameter with your own value on how long the job is allowed to run before it's  aborted:


DECLARE @JobToRunNVARCHAR(128) = 'A Job to run during maintenance window'


--John Huang's Blog



declare @ExecutionStatusTabletable


           JobIDuniqueidentifier NOTNULL, -- Job ID

           LastRunDateint, LastRunTime int, -- Last run date and time

           NextRunDateint, NextRunTime int, -- Next run date and time

           NextRunScheduleIDint, -- an internal schedule id

           RequestedToRunint, RequestSource int, RequestSourceID varchar(128),

           Runningint,  -- 0 or 1, 1 means the job is executing

           CurrentStepint, -- which step is running

           CurrentRetryAttemptint, -- retry attempt

           JobStateint ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions


DECLARE @IsJobRunningBIT =1

DECLARE @job_ownersysname   SET @job_owner= SUSER_SNAME()



DECLARE @ExecutionStatusINT

DECLARE @LastRunOutcomeINT

DECLARE @MaxTimeExceededBIT = 0

DECLARE @TimeoutMinutesINT = 1

EXEC msdb.dbo.sp_start_job @JobToRun

SET @CurrentDateTime= GETDATE()

WHILE 1=1 AND @IsJobRunning=1


           WAITFORDELAY '00:01:00' – how often to check job status, every 1 min

           INSERTINTO @ExecutionStatusTable

           EXECUTEmaster.dbo.xp_sqlagent_enum_jobs 1, @job_owner


           SELECT@IsJobRunning =x.Running

           FROM@ExecutionStatusTable x

           INNERJOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID

  =@JobToRun --your job's name

           IF@IsJobRunning =1

           BEGIN-- job is running or finishing (not idle)

                      SET @CurrentDateTime=GETDATE()

                      IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes



                                 --MSDN sp_stop_job (Transact-SQL)




                                 EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun

                                 -- job stoped, do whatever is needed here




                                 print 'running...' +CONVERT(VARCHAR(100),DATEDIFF(mi, @StartDateTime, @CurrentDateTime))




           IF@IsJobRunning =0 


                      -- job not running, do whatever is needed here

                      print 'job not running'