There has been few instances where the source systems had few glitches, and that led to SSIS packages to cling on connecting (zero timeout) or with a bad data they were processing for more than 4 days.
So there was a requirement where we could put up a monitoring script which could kill any vague running processes beyond a certain threshold and notify support/dev team of Auto Kill action taken by the script.
The following SQL script is run in a job which runs every minute in our BI SQL server to monitor for above instances and greatly helps in monitoring and managing runaway packages.
In the below script we have kept our longest running threshold for 12 hours. So any package can have maximum of 12 hour time frame beyond which it will be auto killed and team will be notified.
DECLARE @exeID INT; DECLARE @duration INT; DECLARE @prj_name VARCHAR(100); DECLARE @pckg_name VARCHAR(100); DECLARE db_cursor CURSOR FOR SELECT execution_id ,DATEDIFF(HOUR , CAST(start_time AS DATETIME), GETDATE()) AS DurationHour, project_name , package_name FROM [SSISServer].SSISDB.catalog.executions WHERE [status] = 2 /* Running */ AND DATEDIFF(HOUR , CAST(start_time AS DATETIME), GETDATE()) > 12; OPEN db_cursor FETCH NEXT FROM db_cursor INTO @exeID , @duration , @prj_name , @pckg_name WHILE @@FETCH_STATUS = 0 BEGIN EXEC [SSISServer].SSISDB.[catalog].[stop_operation] @exeID; declare @subject_text varchar(150), @body_text nvarchar(150); SET @body_text = 'SSIS Package Auto Kill, Project: ' + @prj_name + ', Package: ' + @pckg_name + ', Execution ID: ' + CAST(@exeID AS VARCHAR(50)); exec usp_sqlmail @recipients = 'firstname.lastname@example.org', @subject = 'SSIS Package Auto Kill', @body = @body_text; FETCH NEXT FROM db_cursor INTO @exeID , @duration , @prj_name , @pckg_name END CLOSE db_cursor DEALLOCATE db_cursor
I hope the above helps.