Way 3 - 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 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.

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

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

Way 3: The script task to execute the child package

 In previous post we saw how to call the child package. Drawback of that approach 1 if we have store the package on different location or different server 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.

 In this post we will see how to call the child package using script task.

 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\') insert into  Package_info (Package_nm,Package_Path) values ('Child_Package_5.dtsx','F:\SSIS_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 script task and reading the obj_package variable value and calling the package using c# code.  

We are writing the below

public void Main()                                 {             bool fireAgain = true;                   DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             oleDa.Fill(dt, Dts.Variables["User::obj_package"].Value);             foreach (DataRow row in dt.Rows)             {                 try                 {                     Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();                     Package myPackage = myApplication.LoadPackage(row.ItemArray[0].ToString(), null);                     Dts.Events.FireInformation(0, "Fire Information", row.ItemArray[0].ToString() + " Executed Successfully", string.Empty, 0, ref fireAgain);                     DTSExecResult myResult = myPackage.Execute();                 }                 catch (Exception ex)                 {                     Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                     Dts.TaskResult = (int)ScriptResults.Failure;                 }             }                                                 Dts.TaskResult = (int)ScriptResults.Success;                                 }

Let’s execute this package.

Before execution 

Running the parent package.  

Execution log. 

See the table log.

  

 Keep reading and share your valuable feedback.