Set an SSIS SQL Agent job to unexpected error or failure

Question

I was running a series of SSIS packages in a SQL Agent job on a virtual server when the server was shutdown - hard.

The package execution report shows it as still running even though I believe it is not. I believe this is because the Agent was not able to write whatever end state record which was required for it to show as "Unexpected termination".

1. How do I verify that it is, in fact, not still running?
2. How do I tell the SSIS Reports > All executions that it is not running?

Can I update or insert a record into an SSISdb table?

Answer 1

If you trace the query the report is generating you'll see that the executions are stored in the [catalog].[executions] of the database behind the Integration Services Catalog.

You can just modify your [end_time] there and you should be able to 'fix' the report.

I haven't managed to reproduce your exact scenario (i.e. hard resetting a VM to get a package in the running state) but I could modify the value in that field and the All Executions report reflects the new values.

However, don't set the end_time too much behind the start_time as you'll get overflow errors in the report if your package appears to have run for days.

According to the documentation you have these statuses available to you:

The status of the operation. The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).

So running this:

UPDATE [catalog].[executions]
SET [end_time] = DATEADD(minute, 2, start_time)
    ,[status] = 6
WHERE [execution_id] = 2;

on my system results in a package that shows "unexpected termination" after 2 minutes.