Snippet Name: Stop_SSIS_Instances_Query

Tags:

Created Date: 2025-06-16 13:12:45
Last Modified Date: 2025-05-31 10:32:08

Dependencies:

DBA Notes:

Sample Output:

Title: Stop SSIS Instances Query
Author: user
in sql Server 2019, I have multiple instances in SSISDB "Active Operations", there are lots of instnaces running of the same package named "SSIS", isntea dof stopping htem one at a time, can i have a query tostop all of them withthe same ObjectName of "SSIS"

Author: ChatGPT
Yes, you can stop all running instances of the package named "SSIS" in the `SSISDB` catalog by using the `stop_operation` stored procedure. You first need to identify the running operations for that package from the `internal.operations` table and then execute `catalog.stop_operation` for each of them.

Here???s a query to generate and execute the stop commands for all running instances of the "SSIS" package:

```sql
USE SSISDB;
GO

DECLARE @SQL NVARCHAR(MAX) = '';

-- Generate STOP commands for all running instances of "SSIS" package
SELECT @SQL = @SQL + 
    'EXEC SSISDB.catalog.stop_operation ' + CAST(operation_id AS NVARCHAR(20)) + ';' + CHAR(13)
FROM SSISDB.internal.operations
WHERE status IN (2, 3) -- Running or Pending
AND object_name = 'SSIS';

-- Execute the generated SQL
EXEC sp_executesql @SQL;
```

### Explanation:
- The `internal.operations` table keeps track of currently active SSIS executions.
- The `status IN (2,3)` filter ensures only actively running or pending executions are targeted.
- The `catalog.stop_operation` stored procedure stops each running package.
- The query dynamically builds and executes `STOP` commands for all running instances of the "SSIS" package.

This will stop all active executions of the package named "SSIS" in one go instead of stopping them manually.

Author: user
it seems like all the rows returned return  an error like this: Msg 27124, Level 16, State 1, Procedure internal.prepare_stop, Line 124 [Batch Start Line 50]
Integration Services server cannot stop the operation. The specified operation with ID '2257920' is not valid or is not running

Author: ChatGPT
The error suggests that some of the `operation_id` values are not actually in a valid running state when `catalog.stop_operation` is called. This could happen if:
- The operations are already stopping or completed before the script executes.
- Some rows in `internal.operations` still appear but are not in an actual "runnable" state.

### **Solution**
Modify the script to ensure that only truly active operations are attempted to be stopped.

#### **Updated Query:**
```sql
USE SSISDB;
GO

DECLARE @SQL NVARCHAR(MAX) = '';

-- Ensure we only try stopping valid running operations
SELECT @SQL = @SQL + 
    'EXEC SSISDB.catalog.stop_operation ' + CAST(operation_id AS NVARCHAR(20)) + ';' + CHAR(13)
FROM SSISDB.internal.operations
WHERE status = 2 -- Running (Avoid 3 - Pending)
AND object_name = 'SSIS';

-- Execute the generated SQL only if there are operations to stop
IF @SQL <> ''
    EXEC sp_executesql @SQL;
ELSE
    PRINT 'No running instances of SSIS package found.';
```

### **Changes and Fixes:**
1. **Filter Only Actively Running Operations**  
   - Changed `status IN (2, 3)` to `status = 2` to avoid stopping operations that are in a "Pending" state but may not be actually running yet.
   
2. **Prevent Execution If No Operations Are Found**  
   - If no valid operations exist, it prints a message instead of trying to execute an empty SQL batch.

### **Alternative: If Manual Validation is Needed**
If you want to manually check which operations are in a truly running state before stopping them, run:

```sql
SELECT operation_id, object_name, status, start_time
FROM SSISDB.internal.operations
WHERE status = 2 -- Only running
AND object_name = 'SSIS'
ORDER BY start_time DESC;
```

Then manually stop them with:

```sql
EXEC SSISDB.catalog.stop_operation @operation_id;
```

Let me know if you're still getting the error!