Way 2 - dynamically call the child package using execute package task

We have to develop a package (parent package) which calls the child package.  Our purpose to design this package that once deploy this package on the production server and in the future we want to add new child package or remove an existing child package without changing the production parent package.

There the multiply way to create this package dynamically.

Way 1: using for Foreach loop container and inside this container we will call the execute package task.

https://bageshkumarbagi-msbi.blogspot.com/2020/10/way-1-dynamically-call-child-package.html

Way 2: store the child package information like package name and location in the sql server table and then using the Foreach loop container and inside this container we will call the execute package task.

Way 3: The script task to execute the child package.

Way 2: store the child package information like package name and location in the SQL server table and then using the Foreach loop container and inside this container we will call the execute package task. 

 In the previous post, we saw how to call the child package. The drawback of that approach if we have store the package on different locations or different servers in this case this will not work.

To overcome with this issue, we are approaching the way 2 to call the dynamically child packages.

In the SQL server table we are storing the package information like package name and path.

Below is the table structure.

create table Package_info ( Package_nm varchar(100), Package_Path varchar(1000) )

 Inserting the package information in this table.

insert into  Package_info (Package_nm,Package_Path) values ('Child_Package.dtsx','H:\SSIS1\Child_package\') insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_1.dtsx','H:\SSIS1\Child_package\') insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_2.dtsx','H:\SSIS1\Child_package\') insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_3.dtsx','H:\SSIS1\Child_package\') insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_4.dtsx','H:\SSIS1\Child_package\')

 In the parent package we will table the Execute Sql task and we will store the result set in the object. 

Sql script to get the full path of the package.

select Package_Path+''+Package_nm as Package_Full_Path from Package_info

  

Mapping the result set 

Now we are taking the Foreach loop container and selecting ADO Enumerator. 

We are taking a variable to store the child package location dynamically.

                              

We are storing the result in a variable. 

Inside the Foreach loop we are taking the Execute package task.

The package looks like below. 

We are taking a variable to store the child package location dynamically.

Child package location   

Now we are taking the execute package task. 

Selecting location as File System and creating the package connection string.  

Now we need to create package connections dynamically. Go to the property of this package select the connection string.  

Click ok.

Parent the package is ready to run.

About child packages:

We have 5 child package names as below.

•          child_package.dtsx
•          child_package_1.dtsx
•          child_package_2.dtsx
•          child_package_3.dtsx
•          child_package_4.dtsx

in this package simple we have written the inset statement of the current package. We are inserting the package name in a table

We are ready to run the master package.

Currently we have 5 child packages.

 

Parent package executed successfully.

See the log table 

Now we are adding one more package on the different Location and inserting package information on the table, without doing any changes in the parent package we will execute the newly added package.

Let’s see

We have added the new package on the other location.

 

Now inserting package details on the Package_info table.

insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_5.dtsx','F:\SSIS_Package\')

                                   
Before running the parent package records in log table. 

Now we are running the parent package.

 

Package exceed successfully.

See the table value.   

In case if we want to stop the run two package. Simply we need to remove these packages information from the config table(Package_info) .

See below

We want to stop the below package.

•          child_package_3.dtsx
•          child_package_4.dtsx

Now we are removing these child package from the table.  

Before running the parent package table value as below

     

Now running the parent package.

 

The parent package executed successfully.

See the table value. 

Hope this will help to design the dynamically child package.