HOW TO Determine if an SSIS Package is running in SQL Server

Many companies which use Kourier to populate a SQL Server database (the Operational Data Store, or ODS) from data in their source business systems will then execute processes to build a Data Mart style database from the ODS using custom SSIS packages, SQL Server Analysis Services, or some other external tool. The Kourier SSIS packages typically perform Delete-then-Insert for net change refreshes (rather than Updates), so to ensure that the ODS database is in a consistent state, the secondary processes can be configured to detect if Kourier SSIS packages are running, and if so, wait until they are completed.

This document explains how to query the ODS database to see if any Kourier SSIS packages are currently running.

 

Assumptions

1. The secondary tool can be configured to perform standard T-SQL queries against the ODS database.

 

Background

Whenever any SSIS Package built by Kourier SQL Accelerator starts processing, it updates or inserts a row in the table dbo.kourier_package_control:

• service_name (Primary Key) = the SSIS Package Name

• start_dt = the datetime at which the SSIS Package started running

• end_dt = null

• process_id = the Windows PID of the SSIS package

• computer_name = the Windows computer name on which the package is running (generally the Kourier ETL server)

Whenever any SSIS package completes processing, it updates the row in dbo.kourier_package_control to set the end_dt to the datetime at which the package completed.

 

Procedure

In the tool which builds secondary data from the ODS, add a step at the beginning to test to see if SSIS packages are running.

1. To test to see if a specific SSIS package is running, use the T-SQL command:

   SELECT * FROM kourier_package_control WHERE service_name = 'MY_PKG_NAME'

   If start_dt is not null and end_dt is null then the package is running.

2. To test to see ALL currently running Kourier SSIS packages:

   SELECT * from kourier_package_control WHERE end_dt is NULL

   All returned rows are packages which are currently running.

Based upon the results of the above test, either exit your procedure or have it sleep for a short time and then run the test again.