Get SSIS Package Last Execution Status w/ SQL Job Name
;WITH cteRowNumber AS (
SELECT
e.EXECUTION_ID,
e.FOLDER_NAME,
e.PROJECT_NAME,
e.PACKAGE_NAME,
e.REFERENCE_ID,
e.REFERENCE_TYPE,
e.ENVIRONMENT_FOLDER_NAME,
e.ENVIRONMENT_NAME,
e.[OBJECT_ID],
e.[STATUS],
e.START_TIME,
e.END_TIME,
e.CALLER_SID,
e.CALLER_NAME,
e.SERVER_NAME,
e.MACHINE_NAME,
ROW_NUMBER() OVER(PARTITION BY e.PACKAGE_NAME ORDER BY e.START_TIME DESC) AS RowNum
FROM
SSISDB.CATALOG.EXECUTIONS e
)
SELECT
cte.SERVER_NAME,
cte.EXECUTION_ID [SSIS_EXECUTION_ID],
cte.FOLDER_NAME,
cte.PROJECT_NAME,
cte.PACKAGE_NAME,
j.name AS JOBNAME,
Case cte.[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 [STATUS],
cte.START_TIME,
cte.END_TIME,
DATEDIFF(ss,cte.START_TIME, cte.END_TIME) [DURATION_SEC],
cte.REFERENCE_ID,
cte.REFERENCE_TYPE,
cte.ENVIRONMENT_FOLDER_NAME,
cte.ENVIRONMENT_NAME,
cte.[OBJECT_ID],
cte.CALLER_SID,
cte.CALLER_NAME
FROM
cteRowNumber cte
LEFT JOIN
msdb.dbo.sysjobsteps js ON js.command LIKE '%' + cte.PACKAGE_NAME + '%'
LEFT JOIN
msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE
cte.RowNum = 1
--AND cte.status = 4
ORDER BY
3,4,5;
|
|
|
|
|
|