Get the SQL Server Agent job that has run a particular execution of a deployed SSIS package

Question

I have a SSIS package deployed in SQL Server and there are 3 different SQL Server Agent jobs that runs this package in different steps and schedules.

My question is: if the package is showing as failed in the Integration Services Catalogs -> Reports in one of the execution, is there a way I can identify which is the job that run that execution which caused the package failed (not by cross checking time of failure from the history of the job and the package failed execution time)?

Answer 1

It is not very straight forward. Based on this stack exchange answer, you may try:

SELECT 
 history.*
,ex.* 
,ex.status
, CASE ex.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 as job_status
FROM (
    SELECT 
        h.step_name,  
        -- h.message, 
        h.run_status, 
        h.run_date, 
        h.run_time, 
        SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,PATINDEX('%[^0-9]%',SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,20))-1) ExecutionId
    FROM MSDB.DBO.SYSJOBHISTORY h) history
LEFT JOIN 
SSISDB.CATALOG.EXECUTIONS ex on ex.execution_id = history.ExecutionId
WHERE project_name = '<ssisdb_project_name_here>'

It has many columns which you can ignore by replacing * in select. The important part is to join MSDB.DBO.SYSJOBHISTORY with MSDB.DBO.SYSJOBHISTORY.

Also, this works for project deployment mode and not package deployment mode of SSIS.