Get the status of the last package execution

USE SSISDB
GO
;WITH CTE_STATUS (s_id, s_created, s_active)
AS
(SELECT s_id, s_created, s_active
    FROM (VALUES
     (1, 'Created', 'Active')
    ,(2, 'Running', 'Active')
    ,(3, 'Canceled', 'Failed')
    ,(4, 'Failed', 'Failed')
    ,(5, 'Pending', 'Active')
    ,(6, 'Ended unexpectedly', 'Failed')
    ,(7, 'Succeeded', 'Succeeded')
    ,(8, 'Stopping', 'Active')
    ,(9, 'Completed', 'Failed')) AS S(s_id, s_created, s_active)
)
SELECT TOP (1)
    E.execution_id
,   E.folder_name
,   E.project_name
,   E.package_name
,   E.executed_as_name
,   E.created_time
,   E.status
,   s.s_active
,   cast(E.start_time as datetime) as start_time
,   cast(E.end_time as datetime) as end_time
,   DATEDIFF(minute,cast(E.start_time as datetime), isnull(cast(e.end_time as datetime), GETDATE())) as duration_minutes
,   E.caller_name
,   E.process_id
,   E.stopped_by_name
,   E.server_name
,   E.machine_name
,   E.total_physical_memory_kb
,   E.available_physical_memory_kb
,   E.total_page_file_kb
,   E.available_page_file_kb
,   E.cpu_count
,   F.folder_id
,   F.name
,   F.description
,   F.created_by_name
,   F.created_time
,   P.project_id
,   P.folder_id
FROM catalog.executions AS E
INNER JOIN ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P
    ON P.folder_id = F.folder_id
    AND P.name = E.project_name
LEFT JOIN CTE_STATUS AS S on E.status = s.s_id
WHERE E.package_name = '<your pacakage>'
ORDER BY E.execution_id DESC
GO