question

PhilS-1932 avatar image
0 Votes"
PhilS-1932 asked Zoehui-MSFT edited

How to Get and Cancel a RunningPackage

I am using the Integration Services api to successfully start an SSIS package in the SSISDB. I would like to be able to cancel the package while it is running. The documentation describes a Stop() method on RunningPackage objects that should accomplish this but I have two issues:

  1. Calling Application.GetRunningPackages(serverName) always fails. I have integration services configured for a named instance but when I pass in (local)\myInstance as the argument then I get the error below:

SSIS service does not support multi-instance, use just server name instead of \"server name\\instance\"

If I only pass the server name or pass null like the example I get an error like below:

The specified service does not exist as an installed service.

How do I successfully get this to return RunningPackages for my named instance?

  1. Assuming I can get that to return RunningPackages I then need to determine which one I want to cancel. A RunningPackage has an InstanceID property which sounds like what I want, however when I initially execute the package I see no way to get this InstanceID. To start executing the package I call SSISPackageDetail.PackageInfo.Execute() which returns an identifier but it's a Long while InstanceID is a Guid. How do I get this InstanceID so that I can cancel the correct package?



sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @PhilS-1932,

There is a way to do that. It is possible to Cancel a running SSIS package via SSISDB's a built-in stored procedure called [catalog].[stop_operation] .

It is a two step process.

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

 select * from catalog.executions Where end_time is null

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

 Exec catalog.stop_operation  @operation_id =  11

Check it out here:

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered Zoehui-MSFT edited

Hi @PhilS-1932,

I'm not familiar with code writing, here is a same thread you may take a reference.

stop-ssis-package-at-runtime-net-c

relating-executioninstanceguid-to-the-ssisdb

Hope it could give you some ideas.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.