Hi,
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 = 'team@org.com',
@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.
