Stop an SSIS Package

There are several ways to stop and executing SSIS package. If the package is executing in the SSIS Catalog, you can use the SSISDB.[catalog].stop_operation stored procedure. Stop_operation has one parameter – a bigint named @operation_id. You can execute the stored procedure thus:

exec SSISDB.[catalog].stop_operation @operation_id = <operation_id>;

You can obtain the operation_id of an executing SSIS package in several ways.

One way is from the All Executions report built into SSMS:

Another way is to use my (free) utility – Catalog Reports – which is a SQL Server Reporting Solution (SSRS) solution that allows you to view much of the information contained in the Catalog reports built into SQL Server Management Studio (SSMS). It’s shown here running on SSRS 2016 inside the Microsoft Edge browser:

Once you have the operation_id value, simply plug it into the stop_operation stored procedure and execute:

exec SSISDB.[catalog].stop_operation @operation_id = 24

The stop_operation stored procedure runs for a few seconds (typically less than 15 seconds) and stops the execution of the SSIS package. SSIS packages that have been stopped are listed with “Canceled” status. You can see operation_id 19 was stopped in the screenshots shown above.

:{>

Learn more:

Related Training:
Loading Medical Data to the Cloud, a (FREE!) webinar – 11 Aug 2016 – Register today
IESSIS2: Immersion Event on Advanced SQL Server Integration Services

Related Articles:
Stairway to Integration Services
SSIS 2016 Administration: Create the SSIS Catalog
SQL Server 2016 Developer Edition is Free
Converting an SSIS 2014 Solution to SSIS 2016
Installing SQL Server 2016 Developer Edition, One Example
Deploying a Single Package First in SSIS 2016 Announcing SSIS Catalog Reports v0.1
SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
Stairway to Biml

Related Books

Help!
Enterprise Data & Analytics – We are here to help.