Execute package task in SSIS

A child package is a package executed from another package. The package that executes another package is called a parent package. The parent package calls the child package by using the Execute Package Task in the Control Flow.

The benefit of using the parent package and child package execution.

1.  We can break our complex work into smaller work.
2.   Easy to maintain the packages.
3.   Reuse the existing child package on other project.
4.   Controlling the package security.

Let’s see the example

We have a data warehouse in that we need to load the data into the dimension and facts table. Think that we are getting the data from the heterogeneous sources and we need to load first in the stage after that we need to load it into the data warehouse.

Dimension

Ø  Date

Ø  Customer

Ø  Store

Ø  product

 Fact

Ø  Sales             

Imagine if we write only one package to load from stage to data warehouse. How it will be complex. It is a bit difficult to manage.

It will be easy if we split the package and create a small package for each load like

Ø  DimDate.dtsx to load the dim date

Ø  DimCustomer.dtsx to load the customer details

Ø  DimStore.dtsx to load the store details

Ø  DimProduct.dtsx to load the product details

Ø  LoadFact.dtsx with the help of execute package task, we are calling the dim package and load in dim table and we write the transformation to load the data into the fact sales table.

Here LoadFact.dtsx package is called a Parent package and Dim packages are known as Child packages.

See the demo

We are creating these package. For the demo, we are taking script tasks to show the message like the child package is executing.

    

Below is the list of packages.

Now we are calling these package to the main package.

    

Now I am configuring the child package.

Double click on the execute package task. Go to the package and configure the values.

Reference Type:

Ø  Project Reference: It is introduced in 2012 a version of ssdt. We can call the package which is in the current project.

Ø  External Reference: we can call the package which is store in the file location or store in the SQL server.

           

Here we are using the Project Reference.

Package Name from Project Reference: Select the child package name.

       

  Selecting the Child package name “DimDate.dtsx”.

If the child package is protected by a password then we need to provide the password here.

     

If we have any variable which we want to pass from the parent package to child package we need to bind the parameter values in the Parameter bindings tab.

    

Now click ok.

    

Now the package is ready to execute.

     

Similarly, it executes all child packages.

   

Hope this will help to design the complex task into multiple small works of the unit and call then into one package.