Status Column in SSISDB Catalog Database

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
;

 

SSISDB catalog execute status

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s