Package execution log

• catalog.executions reports package executions (view combining [internal].[executions] and [internal].[operations])
• catalog.operation_messages reports execution events (and others) (view combining [internal].[operation_messages] and [internal].[operation_messages_scaleout])

;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

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:

;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