A fish out of water: How to check what SSIS packages are running and stop them?

Posted: June 23, 2023  |  Categories: SQL Server

You may already know that I usually use the series A fish out of water when I want to write something that goes a little bit off-topic on my main blog topic: Enterprise Integration. This time, and despite this, can also be considered an Enterprise Integration – ETL process – I don’t consider myself a SQL Server “expert”. I often delegate these tasks to my data team. However, this week one of my clients call me regarding an issue we were facing with our integration platform, which is mainly composed of SQL Server, BizTalk Server, and Azure.

While diagnosing the problem – almost feeling like Dr. House – I realize we were not getting any new data because the ETL jobs failed with the error: There is already an active instance of this package.

That happened because we were controlling the execution of the package and not allowing multiple executions of the same package to coincide by doing the following validation.

IF (SELECT COUNT(*) AS ExecutionCount
 FROM SSISDB.catalog.executions
	WHERE status = 2 
	AND folder_name = '<folder-name>'
	AND package_name = '<package-name>.dtsx') > 0 
BEGIN
	THROW 50000, 'There is already an active instance of this package.', 1;
END

The problem was that I didn’t knew at that time how to monitor which SSIS packages were currently running and how to stop them. Because for some reason, I’m guessing network issues, those packages were kind of zombies. And I didn’t have my team available at that time. Because that was a production environment, I had to learn, which is also good! And this may be a helpful tip for other situations where we need to check what are the SSIS Packages running from the Catalog and want to subsequently stop them.

To accomplish that, we need to:

  • On current versions of Windows, on the Start page, type SSMS and then select Microsoft SQL Server Management Studio.
    • When using older versions of Windows, on the Start menu, point to All Programs, point to Microsoft SQL Server, and then select SQL Server Management Studio.
  • On the Object Explorer panel, expand Integration Services Catalogs, right-click on SSISDB, and select the Active Operations option from the options menu.
  • A new Active Operations window will open, presenting all the running packages
  • From this window, you can select the package you want and click the Stop button to force stopping that SSIS package execution.

It’s also possible to do the same process via T-SQL by running these to queries:

  • Query to retrieve all currently running packages in the SSIS.Catalog
SELECT * FROM SSISDB.catalog.executions WHERE end_time IS NULL
  • Query to stop the execution of a specific SSIS package
EXEC SSISDB.catalog.stop_operation @operation_id =  <id>

Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top