Find SSIS Package Stuck in Running Status

CREATE PROCEDURE [dbo].[ssis_monitor]
----------------------------------------------------------------------------------------------------------------------------
-- LOCATION : SSIS servers 
-- AUTHOR : Aakash Patel
-- DATE : 05/09/2018
-- INPUTS : 
-- OUTPUTS : 
-- DEPENDENCIES : none
-- DEPENDENTS : none
-- DESCRIPTION : This proc checks for long running ssis executions and stops them based on the defined threshold from dbo.ssis_monitor_configure table
-- which app calls this proc  : agent job
-- FREQUENCY : every xx seconds
-- MODIFICATION HISTORY : 
-- 05/09/2018 – Aakash Patel : Initial Build 
----------------------------------------------------------------------------------------------------------------------------
AS
BEGIN
  BEGIN TRY
    DECLARE @execution_id bigint
    DECLARE @package_name nvarchar(100)
    DECLARE @tableHTML varchar(max)
    DECLARE @emailsubject AS varchar(150)
    DECLARE @alertemail varchar(50)
    DECLARE @dbaOncall varchar(50) = '[email protected]'  --email address for on call device
    DECLARE @oncallthreshold tinyint = 5  --threshold for long running instances to alert on call device

    -- load long running package information in a temp table

    SELECT
      ex.execution_id,
      ex.package_name,
      ex.environment_name,
      ex.caller_name,
      DATEDIFF(SECOND, CAST(ex.start_time AS datetime2), SYSDATETIME()) AS 'run_time_sec',
      smc.threshold_time_sec,
      smc.alert_email INTO #execution_info
    FROM ssisdb.catalog.executions(nolock) ex
    JOIN dbo.ssis_monitor_configure(nolock) smc
      ON ex.package_name = smc.package_name
    WHERE ex.end_time IS NULL  -- still running 
    AND ex.status = 2 -- ( 1-created , 2-running ,3-canceled,4-failed ,5-pending,6-ended unexpectedly,7-succeeded ,8-stopping, 9-completed )
    AND (ex.environment_name = smc.environment_name
    OR smc.environment_name IS NULL)
    AND (DATEDIFF(SECOND, CAST(ex.start_time AS datetime2), SYSDATETIME()) > smc.threshold_time_sec);

    -- alert DBA On call if long running instances are higher than threshold 
    IF (@@ROWCOUNT > @oncallthreshold)
    BEGIN
      SET @emailsubject = 'SSIS Alert: ' + @@servername + ': More than ' + CAST(@oncallthreshold AS varchar(10)) + ' long running SSIS executions detected.'
      EXEC msdb.dbo.sp_send_dbmail @recipients = @dbaOncall,
                                   @subject = @emailsubject,
                                   @body_format = 'HTML';
      RETURN;
    END
    -- loop through long running executions and stop them
    ELSE
    BEGIN
      DECLARE ssis_monitor_cursor CURSOR FAST_FORWARD FOR
      SELECT
        execution_id,
        package_name,
        alert_email
      FROM #execution_info;
      OPEN ssis_monitor_cursor;

      FETCH NEXT FROM ssis_monitor_cursor INTO @execution_id, @package_name, @alertemail;

      WHILE @@fetch_status = 0
      BEGIN

        --stop the long running execution
        EXEC ssisdb.catalog.stop_operation @operation_id = @execution_id;

        --build email body
        SET @emailsubject = 'SSIS Alert: ' + @@servername + ': ' + @package_name + ' is past defined threshold and has been stopped';
        SET @tableHTML = N'<table border="2" cellpadding="2" cellspacing="0">' + CHAR(13);
        SET @tableHTML = @tableHTML + N'<tr><th>execution_id</th><th>package_name</th><th>environment_name</th><th>calling_user</th><th>exec_time_seconds</th><th>threshold_sec</th></tr>' + CHAR(13);
        SET @tableHTML = @tableHTML + CAST((SELECT
          execution_id AS td,
          package_name AS td,
          COALESCE(environment_name, 'not defined') AS td,
          caller_name AS td,
          run_time_sec AS td,
          threshold_time_sec AS td
        FROM #execution_info
        WHERE execution_id = @execution_id
        FOR xml RAW ('tr'), ELEMENTS)
        AS nvarchar(max)) + CHAR(13);
        SET @tableHTML = @tableHTML + N'</table>' + '<br>' + CHAR(13);

        --- notify dba group 
        EXEC msdb.dbo.sp_send_dbmail @recipients = @alertemail,
                                     @subject = @emailsubject,
                                     @body = @tableHTML,
                                     @body_format = 'HTML';

        FETCH NEXT FROM ssis_monitor_cursor INTO @execution_id, @package_name, @alertemail;

      END;

      CLOSE ssis_monitor_cursor;
      DEALLOCATE ssis_monitor_cursor;
    END;

  END TRY

  ----- error handling 
  BEGIN CATCH
  --insert your standard error handling code
  END CATCH
END;