Begin and End date times in ssis child packages

I am looking through the SSIS DB Project Catalog to find the Begin and End datetime for every SSIS package called from a Master package. Is there any query to conduct this?

After looking at [internal].[executions], and SSISDB.catalog.operation_messages, etc

Running this query does not work, if I there is a master package calling Child Packages.

select start_time,end_time,*
from catalog.executions cc order by cc.start_time desc

I am trying to find the begin and end time for every child pacakge.

Solution

The CATALOG.EXECUTABLE_STATISTICS DMV logs execution statistics for components within a package, including execute package tasks. The START_TIME and END_TIME columns store the time that the component began and completed execution. The EXECUTION_DURATION column holds the time that an executable, in this case the child packages, took to execute in milliseconds. This can of course be converted to seconds, minutes, etc. depending on what you need. While this has a column for the execution path of the component within the parent package, it doesn't have a column for the direct name of component, thus CATALOG.EXECUTABLES is included for the EXECUTABLE_NAME, and this DMV can be omitted if you only want to view the execution path (EXECUTION_PATH column) instead. CATALOG.EXECUTIONS has columns for the folder and project name, and you can join to this to apply filters for the specific project and folder that the package is located in. You can also apply a filter on the EXECUTION_ID column to only view details for a specific execution. Executing a package at the basic logging level with allow execution details to be logged for the components.

SELECT 
    EX.FOLDER_NAME,
    EX.PROJECT_NAME,
    E.EXECUTABLE_NAME,
    EX.PACKAGE_NAME,
    ES.START_TIME AS ComponentStartTime,
    ES.END_TIME AS ComponentEndTime,
    EX.start_time AS PackageStartTime,
    EX.end_time AS PackageEndTime,
    ES.EXECUTION_DURATION AS ComponentExecutionTimeInMilliseconds
FROM SSISDB.CATALOG.EXECUTIONS EX 
INNER JOIN SSISDB.CATALOG.EXECUTABLES E on EX.EXECUTION_ID = E.EXECUTION_ID
INNER JOIN SSISDB.CATALOG.EXECUTABLE_STATISTICS ES on E.EXECUTABLE_ID = ES.EXECUTABLE_ID AND EX.EXECUTION_ID = ES.EXECUTION_ID
--PACKAGE_NAME- parent package
WHERE E.PACKAGE_NAME = 'Package Name.dtsx' AND EX.PROJECT_NAME = 'Project Name' 
AND EX.FOLDER_NAME = 'Folder Name'