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: 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:

SET NOCOUNTON 

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


------------------------------------------------------


--John Huang's Blog


--http://www.sqlnotes.info/tag/xp_sqlagent_enum_jobs/


------------------------------------------------------


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 @StartDateTimeDATETIME = GETDATE()

DECLARE @CurrentDateTimeDATETIME


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


BEGIN


           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


                      BEGIN     


                                 -------------------------------------------


                                 --MSDN sp_stop_job (Transact-SQL)


                                 --http://msdn.microsoft.com/en-us/library/ms182793.aspx


                                 -------------------------------------------


                                


                                 EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun


                                 -- job stoped, do whatever is needed here


                      END


                      ELSE


                      BEGIN


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


                                 CONTINUE


                      END


           END


           IF@IsJobRunning =0 


           BEGIN


                      -- job not running, do whatever is needed here


                      print 'job not running'


           END


END

Rate

Share

Share

Rate