Introduction

In this post we are going to cover a way to terminate an SSIS package that is running.

One of the main uses of this that I have found was to terminate SSIS packages that do not seem to run properly and run for much longer than they should. I have seen this happen several times when a SSIS package is stored in SQL Server and is scheduled to run using SQL Agent. If conditions are right, these packages can run indefinitely. It seldom happens, but it is possible. Unless you are checking the logs constantly, these processes can run until someone finds them and terminates them manually.

To make things much more automated, I wrote a short script to terminate any of these runaway processes. The way I set up this script is in a separate SQL Agent job that I run on a scheduled basis. If a package is ran overnight and should only take an hour to run, I may set this script up to run in a different SQL Agent job a couple hours after the other package. This way if the package takes an hour and fifteen minutes for some reason, I am not terminating it too early.

How To Terminate an SSIS Operation

This script assumes you are using SSIS and have the SSISDB installed on the same server as the SQL Agent. The SSISDB database has a stored procedure called stop_operation we can use to terminate any running SSIS package.

-- Terminate an SSIS operation with id of 15
[SSISDB].[catalog].[stop_operation] 15;

As you can see in the code above, it requires that we provide it the ID of the process we want to terminate. In order to find the id of a process, we can query the [catalog].[executions] table. The only records we care about though are the running processes. Running processes have a [status] of 2.

SELECT *
FROM [SSISDB].[catalog].[executions] 
WHERE [status] = 2
ORDER BY [execution_id];

The only field names we really care about for our purposes are the [execution_id], [folder_name], [project_name], [package_name], and [status].

The [execution_id] is the ID we need to pass to the stop_operation stored procedure in order to terminate the operation.

The [folder_name], [project_name], and [package_name] give specific information about where the SSIS package was published to on the server. We want to look for a specific package we are going to terminate if it is running, so you will need to look this information up to put in the script.

Now, if we put all the above information together, we will get the finished script.

DECLARE @ExecutionId INT;

SELECT TOP 1 
	  @ExecutionId = execution_id
FROM [SSISDB].[catalog].[executions] 
WHERE
	[folder_name] = 'SSISFolder' AND 
	[project_name] = 'ETL' AND
	[package_name] = 'ETL Package.dtsx' AND
	[status] = 2 -- This is the status for running
ORDER BY [execution_id]; -- Only necessary if there are multiple process of this same package running. 

IF @ExecutionId IS NOT NULL
BEGIN
	DECLARE @KillQuery NVARCHAR(2000);

	-- Kill the process
	SET @KillQuery = CONCAT('[SSISDB].[catalog].[stop_operation] ', @ExecutionId, ';');

	EXECUTE(@KillQuery);
END

I don't have a comments section yet, so feel free to send me feedback on this blog.