Friday, June 1, 2018

SSIS - How to call multiple child packages by parent or master package

In our day to day activities, we can build a data extraction module that can be called from different packages. 
For example, we have to load the data into a star schema, and we can build a separate package to populate each dimension and the fact table and these packages are located in some folder. They should be executed in certain order one by one.
Now, we want to create one master SSIS package that will go to that folder and grab those child packages and execute or run them one by one (no repeat) in a predefined order.
To accomplished this, You could build a Foreach Loop Container with a Package Execute Task in it to execute them, then we would need to name them so that they were retrieved in the order we wanted. 
With the help of variables, we can set the package name property in Package Execute Task and this variable will get the next value from the Foreach loop Container.

Foreach Loop Container can get these package names from a data table that contained the order we wanted and then we populate an SSIS object type variable with the record-set and use that to feed the order and the list to a Foreach loop just like we mentioned above. The only difference would be the source of the list.

The below video is capable to explain that How can we call multiple child packages by parent or master package?

No comments:

Post a Comment