SSIS : Find execution of child package

Question

Hi, I have a SSIS project with three packages :

• Parent.dtsx
• Child1.dtsx
• Child2.dtsx

Parent execute Child1 and Child2.

Child1 and Child2 can be executed by Parent, but also directly in SQL Server Management Studio or DTExec.exe or SQL Command...

When I open in SSIS Catalog Reports -> Standard Reports -> All executions on Child1 (or Child2), I don't see the execution started from Parent. Same in the view SSISDB.catalog.executions, no row for execution started from Parent.

From the documentation of the task Execute Package Task :

By default, the ExecuteOutOfProcess property of the Execute Package task is set to False, and the child package runs in the same process as the parent package. If you set this property to True, the child package runs in a separate process.

I try set ExecuteOutOfProcess on true on task executing child package wit no success. When the child is executed by the parent, this don't add entry in the view SSISDB.catalog.executions.

How execute child package from parent package and the child execution visible in the view SSISDB.catalog.executions?

If isn't possible, how can retrieve execution result of child package executed from parent package (with a SQL query)?

Answer 1

Hi @Vernou ,

We can use the following sql query to get the execution result of child packages in SSMS.(Execution_result 0 (Success))

We need two tables: catalog.executable_statistics and catalog.executions (SSISDB Database).

Use SSISDB;  
Select a.execution_id, a.folder_name, a.project_name, a.package_name, b.execution_path, b.start_time, b.end_time, b.execution_duration, b.execution_result  
From [SSISDB].[internal].[executions] As a  
Inner Join [SSISDB].[internal].[executable_statistics] As b   
On (a.execution_id=b.execution_id)  
ORDER BY a.execution_id DESC;  

Best Regards,
Mona

Answer 2

Hi @Vernou ,

Please set the logging level as basic or verbose while executing the Parent package in SSISDB Catalog.

Then we can see the execution information about Child packages on the report of the Parent package.

Hope the following pictures will be helpful:

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?