====== Package execution log ======
{{tag> ssis}}

  * ''[[https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database|catalog.executions]]'' reports package executions (view combining ''[internal].[executions]'' and ''[internal].[operations]'')
  * ''[[https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-operation-messages-ssisdb-database|catalog.operation_messages]]'' reports execution events (and others) (view combining ''[internal].[operation_messages]'' and ''[internal].[operation_messages_scaleout]'')

===== Querying the log =====

<code tsql>
;WITH cte AS (
  SELECT 
    x.execution_id
  , x.folder_name + '\' + x.project_name + '\' + x.package_name AS package_name
  , CAST(x.start_time  AS DATETIME) AS start_time  
  , CAST(x.end_time  AS DATETIME) AS end_time  
  , CASE x.[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 [status]
  , CAST(m.message_time AS DATETIME) AS message_time
  , CASE m.message_type
    WHEN -1 THEN 'Unknown'
    WHEN 120 THEN 'Error'
    WHEN 110 THEN 'Warning'
    WHEN 70 THEN 'Information'
    WHEN 10 THEN 'Pre-validate'
    WHEN 20 THEN 'Post-validate'
    WHEN 30 THEN 'Pre-execute'
    WHEN 40 THEN 'Post-execute'
    WHEN 60 THEN 'Progress'
    WHEN 50 THEN 'StatusChange'
    WHEN 100 THEN 'QueryCancel'
    WHEN 130 THEN 'TaskFailed'
    WHEN 90 THEN 'Diagnostic'
    WHEN 200 THEN 'Custom'
    WHEN 140 THEN 'DiagnosticEx'
    WHEN 400 THEN 'NonDiagnostic'
    WHEN 80 THEN 'VariableValueChanged'
    END AS msg_type
  , CASE m.message_source_type
    WHEN 10 THEN 'Entry APIs, such as T-SQL and CLR Stored procedures'
    WHEN 20 THEN 'External process used to run package (ISServerExec.exe)'
    WHEN 30 THEN 'Package-level objects'
    WHEN 40 THEN 'Control Flow tasks'
    WHEN 50 THEN 'Control Flow containers'
    WHEN 60 THEN 'Data Flow task'
    END as msg_source
  , m.[message]
/*
  , (
    select 
      '{' + string_agg('"' + REPLACE(REPLACE(parameter_name, '\', '\\'), '"', '\"') + '":"' 
      + REPLACE(REPLACE(CAST(parameter_value AS NVARCHAR(MAX)), '\', '\\'), '"', '\"')
      + '"', ',') + '}'
    from catalog.execution_parameter_values pv
    where pv.execution_id = x.execution_id
    ) AS execution_parameter_values
--*/
  FROM [catalog].executions x
    INNER JOIN [catalog].operation_messages m ON m.operation_id = x.execution_id
)
SELECT TOP 1000 *
FROM cte
ORDER BY [message_time] DESC
</code>

===== db_datareader? ====
The SSISDB ''catalog'' views are filtered on the basis of your SSIS-specific database role memberships -- if you're a member of db_datareader, you won't get any results! Try this:

<code tsql>
;WITH cte AS (
  SELECT 
    x.execution_id
  , x.folder_name + '\' + x.project_name + '\' + x.package_name AS package_name
  , CAST(o.start_time  AS DATETIME) AS start_time  
  , CAST(o.end_time  AS DATETIME) AS end_time  
  , CASE o.[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 [status]
  , CAST(m.message_time AS DATETIME) AS message_time
  , CASE m.message_type
    WHEN -1 THEN 'Unknown'
    WHEN 120 THEN 'Error'
    WHEN 110 THEN 'Warning'
    WHEN 70 THEN 'Information'
    WHEN 10 THEN 'Pre-validate'
    WHEN 20 THEN 'Post-validate'
    WHEN 30 THEN 'Pre-execute'
    WHEN 40 THEN 'Post-execute'
    WHEN 60 THEN 'Progress'
    WHEN 50 THEN 'StatusChange'
    WHEN 100 THEN 'QueryCancel'
    WHEN 130 THEN 'TaskFailed'
    WHEN 90 THEN 'Diagnostic'
    WHEN 200 THEN 'Custom'
    WHEN 140 THEN 'DiagnosticEx'
    WHEN 400 THEN 'NonDiagnostic'
    WHEN 80 THEN 'VariableValueChanged'
    END AS msg_type
  , CASE m.message_source_type
    WHEN 10 THEN 'Entry APIs, such as T-SQL and CLR Stored procedures'
    WHEN 20 THEN 'External process used to run package (ISServerExec.exe)'
    WHEN 30 THEN 'Package-level objects'
    WHEN 40 THEN 'Control Flow tasks'
    WHEN 50 THEN 'Control Flow containers'
    WHEN 60 THEN 'Data Flow task'
    END as msg_source
  , m.[message]
/*
  , (
    select 
      '{' + string_agg('"' + REPLACE(REPLACE(parameter_name, '\', '\\'), '"', '\"') + '":"' 
      + REPLACE(REPLACE(CAST(parameter_value AS NVARCHAR(MAX)), '\', '\\'), '"', '\"')
      + '"', ',') + '}'
    from catalog.execution_parameter_values pv
    where pv.execution_id = x.execution_id
    ) AS execution_parameter_values
--*/
  FROM internal.executions x
    INNER JOIN [internal].[operations] o ON x.[execution_id] = o.[operation_id]
    INNER JOIN (
      SELECT 
        operation_id
      , message_time
      , [message]
      , message_source_type
      , message_type
      FROM internal.operation_messages m

      UNION ALL 

      SELECT 
        operation_id
      , message_time
      , [message]
      , message_source_type
      , message_type
      FROM internal.operation_messages_scaleout m
    ) m ON m.operation_id = x.execution_id
)
SELECT TOP 1000 *
FROM cte
ORDER BY [message_time] DESC
</code>