Executing same child package parallely inside ForEach Loop from parent package in SSIS

Question

I have parent package designed in such a way that step 1: Data flow task fetches the list of configuration from db and stores in recordset. step 2: for each loop having child package being called with it's project parameters set with values from previous task.

Now the scenario: if I get 2 records from step 1 then step 2 executes 2 times sequentially. How to execute the step 2 parallely for 2 configurations fetched from step1.

After googling for some amount of time, I copied child execute package task 2 times inside for each loop but the values assignment from previous task to child package parameter is not mapping correctly. Please advise what I am doing incorrectly

Note: I am looking for workaround on Execute package task which calls the copies of child package inside for each loop of parent package to run asynchronously. The child package is generic and requires parameter binding where variable values from parent package is assigned to child package parameter values.So, each copy of child package should be able to fetch different variable value from the list and do parameter binding. Please let me know if this is possible. Execute package task parameter bindings

Answer 1

In your foreach loop add an execute SQL and add this code and fill the correct info and parameters...

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name=N'Package1.dtsx'
    , @project_name=N'Project1'
    , @folder_name=N'Folder1'
    , @use32bitruntime=False
    , @reference_id=NULL
    , @execution_id=@execution_id OUTPUT

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

This will kick off the package and NOT wait.

If you have package parameters to set, add this (between create and start) foreach one:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id
    , @object_type = 30                     -- package parameter
    , @parameter_name = N'ParameterName'
    , @parameter_value = 1