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

SQL Development Wizard

My name is Peter Skoglund. I have twenty+ years experience in IT and I have work with SQL Server since version SQL Server 7.0. I work as a development DBA and spend all my days with development and database administration. My quote is "The first step to improvement is to have the gut to question".

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: http://nirav.extreme-advice.com/2013/05/08/schedule-ssis-package-without-deploying/

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  http://msdn.microsoft.com/en-us/library/ms187320.aspx).

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
           WHEREsj.name =@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'


Leave a comment on the original post [sqldevelopmentwizard.blogspot.com, opens in a new window]

Loading comments...