stop a DTS package programmatically (using C#)

Question

Hi, Is there a way to stop a SQL Server 2000 DTS (not SSIS) package that is currently running, programmatically using C#?. I think the package can be executed using the COM API, but I couldn't find a way to stop a running package. thanks in advance.

Update: there is a method in SSIS to get a list of running packages and a method to stop them. but I couldn't find an equivalent one for DTS packages in COM object API.

Answer 1

Here are a few options:

1. If you are running a DTS package in a job, then you can stop the running job with sp_stop_job.

2. If you know the SPID of the process for the DTS package, you can issue a KILL command to the SPID and that will stop the DTS package execution. One possible way to identify the SPID is to find the SPID in the master.dbo.sysprocesses table by some identifying piece of information and then issue a KILL command for the SPID. Here's a set of code that I tested by setting the Connection Properties -> Advanced -> Application Name to I Want To Kill This SPID.

   DECLARE @TEST INT;

   SET @TEST =

   (SELECT MAX(SPID)

   FROM master.dbo.sysprocesses

   WHERE program_name IN ('I Want To Kill This SPID'));

   IF @TEST IS NOT NULL

   BEGIN

   DECLARE @KILL_COMMAND NVARCHAR(100);

   SET @KILL_COMMAND = 'KILL ' + CONVERT(NVARCHAR(100), @TEST);

   EXEC sp_executeSQL @KILL_COMMAND;

   END

   The downside of the above approach is that it only works on killing packages that have database connections. If you are using a DTS package that does not connect to the host server, then there won't be a killable SPID. If there are multiple SPIDs, this also won't kill them all. You may have to modify the code to build a list of SPIDs to kill. Then there is the fact that you have to set the Application Name. You may want to set unique values for each DTS Package so that you don't accidentally kill the wrong package. The default value appears to be set to DTS Designer which would be bad to kill if multiple packages are running on the server.

3. You might be able to start and kill DTS 2000 packages using extended stored procedures, but I am not 100% sure this is feasible.