There are a handful of reasons for an “Ended Unexpectedly” package execution status in the SSIS Catalog. One reason is: the SSIS package was reporting a “Running” status when the SQL Server instance started.

I can hear some of you thinking,…

“How Did I Get Here?”

The day earlier, I was testing some new SSIS Framework functionality. The tests were failing, marking the applications as failed but leaving the execution status in a Running state. As my Chinese friends can attest, this is 不好 (Bù hǎo) – “not good.” I used a VM for the testing. At the end of the day, I shut everything down.

This morning, when I started the same VM, I saw the error messages displayed above. You can get to the SSIS Catalog All Operations report by:

1. Open SSMS, navigate to Object Explorer>Integration Services Catalogs>SSISDB (right-click)
2. Hover over Reports
3. Hover over Standard Reports
4. Click All Operations

The All Operations Report surfaces general messages, as shown in the first figure, but you may wish to dive deeper. One way to dive deeper is to write a query, such as:

Use SSISDB
go

Select *
From [catalog].operation_messages
Order By operation_message_id desc

My results appear as shown here:

The message column includes a message for each of the four errors shown in the All Operations Report (shown in the first figure):

Warning: During startup, the Integration Services server marked operation <operation_id> (type 200, status 2) as terminated. Please check the operating system error logs for operation details.

In my case, the testing left four SSIS packages stranded in a Running state, so there were no additional “operation details” in the “operating system error logs.”

Conclusion

There are other reasons for “Ended Unexpectedly” package execution statuses in the SSIS Catalog. In this case, the “Ended Unexpectedly” statuses were self-inflicted, but yours may not be. Check out the Event Viewer for help. Use the message_time column in the All Operations Report to isolate more information about errors and warnings, as shown here:

The SSISDB stored procedure named sp_ssis_startup may be configured to run at SQL Server instance startup. This setting is configurable. I recommend selecting this handy feature when you create or modify an SSIS Catalog.

Want to Chat?

Schedule a 30-minute introductory call with me to discuss your project, or your ADF or SSIS performance challenges – it’s free!