Cancel the execution of SSIS Package from a C# application

Question

I have a .NET WinForms application that executes a few SSIS packages created in Package deployment model using Microsoft.SqlServer.Dts.Runtime > DtsContainer.Execute(Connections, Variables, IDTSEvents, IDTSLogging, Object) Method in sequence.

Is there a way to cancel the execution of a package?

Answer 1

It is possible by calling SSIS built-in stored procedure.

It requires to know SSIS package execution_id.

You can call that stored procedure from your application.

Check it out:

USE SSISDB;
GO

DECLARE @execution_id INT = (
SELECT TOP(1) execution_id
FROM CATALOG.executions as x
WHERE folder_name = 'folder_name'
    AND project_name = 'project_name'
    AND package_name = 'package_name.dtsx'
    AND end_time IS NULL
    AND status IN (2, 5) -- 'Running', 'Pending' and 'Active' executions
ORDER BY execution_id DESC);
 
EXEC [catalog].[stop_operation] @execution_id;