I occasionally review the search terms people use to get to this blog and secretly celebrate knowing my post helped them. If i know it didn’t, i add it to the list of items i should write about. This post is a result of one such thing.

Note: If you read my previous post about Logging in SSIS2012, please stop reading here. This doesn’t have any new information. I wrote this one first, but published the Logging post ahead of this and still publishing this to reach Googlers.

I assume you know about the SSISDB catalog database. Several tables/views have a column called [status] showing the status of an execution. It is an integer column with 1 through 9 as the possible values.

Description for these numbers isn’t available anywhere in the database but buried in books online like this.

Here is an example query you can use to translate the status numbers to descriptions.

USE SSISDB ;

SELECT
     project_name  
     [status description]
     = CASE [status]
               WHEN 1 THEN 'created'
               WHEN 2 THEN 'Running'
               WHEN 3 THEN 'canceled'
               WHEN 4 THEN 'failed'
               WHEN 5 THEN 'pending'
               WHEN 6 THEN 'ended unexpectedly'
               WHEN 7 THEN 'succeeded'
               WHEN 8 THEN 'stopping'
               WHEN 9 THEN 'completed'
     END
FROM   [catalog] .[executions]
WHERE  execution_id = 30032 -- replace with your execution_id
;

Another example is with [Catalog].[Executable_Statistics]. Status column in this view shows the execution status of an executable. An executable is any package or a container or a task.

SELECT  e.package_name ,
e.[executable_name] ,
es.[execution_path] ,
es.[execution_duration] ,
[execution_result] = CASE es.[execution_result]
WHEN 0 THEN 'Success'
WHEN 1 THEN 'Failure'
WHEN 2 THEN 'Completion'
WHEN 3 THEN 'Cancelled'
END
FROM    [catalog].[executables] e
INNER JOIN [catalog].[executable_statistics] es ON es.[executable_id] = e.[executable_id]
WHERE   e.execution_id = 40044

@SamuelVanga