We have to develop a package (parent package) which calls the child package. Our purpose to design this package that once deploys this package on the production server and in future we want to add a new child package or remove an existing child package without changing the production parent package.
There are many ways to create this package dynamically.
Way 1: using for Foreach loop container and inside this the container we will call the execute package task.
Way 2: store the child package information like the 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 1: using for Foreach loop container and inside this container, we will
call the execute package task.
We have a parent package
We are taking a variable to store the child package location dynamically.
Below is the Foreach loop configuration.
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 selects the connection string.
Click ok.
The parent package is ready to run.
About child packages:
We have 4 child package names as below.
- child_package.dtsx
- child_package_1.dtsx
- child_package_2.dtsx
- child_package_3.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 4 child packages.
The parent package executed successfully.
See the log table
Now we are adding one more package, without doing any changes
in the parent package, we will execute the newly added package.
Let’s see
We have added the new package to the same location.
Before running the parent package records in the log table.
Now we are running the parent package.
The package executed successfully.
See the table value.
In case if we want to stop the run two packages. Simply we
need to remove these packages from the package location.
See below
We want to stop the below package.
- child_package_3.dtsx
- child_package_4.dtsx
Now we are removing these child packages from the location.
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.
