Stop SSIS Package execution in SSIS.Catalog

[Originally posted by]: https://sabigdata.wordpress.com/2015/02/12/stop-ssis-package-execution-in-ssis-catalog/

This article is about explaining how to cancel or stop a job that was executed directly from the SSIS.Catalog.

I executed a SSIS Package directly from the SSIS.Catalog where I subsequently had to cancel the process. My research lead to the following two options. You can choose which one will work for your situation.

Option 1

Information about the packages are stored in the SSISDB. To view the package information you can query the Catalog.Executions view in the SSISDB.

The following query will return all currently running packages in the SSIS.Catalog

select * from catalog.executions Where end_time is null

You need to use the execution_id found in the result set to stop the package using an internal SSISDB stored procedure.

All_Executions_Query_Results

To determine if you have the correct execution_id run the All_Executions report in the SSIS.Catalog, identify the correct ssis package that is executing and click on the Overviewhyperlink.

All_Executions

The drill down report will give you the execution_id of the currently running process.

All_Executios_Overview

The following T-SQL statement will stop the execution of the SSIS package.

Exec catalog.stop_operation  @operation_id =  11

Option 2

When you right click on the SSISDB in the Integration Services Catalogs click on Active Operations. A new window will open with a Stop button where you can cancel the specific ssis package that is executing.

ActiveOperations

Also see the Microsoft documentation related to this topic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s