SSIS – Dynamically Sequential Execution of Child Package

Introduction

Now it is the time of Executing the package. But we have several package and we want to execute it sequentially. For that we are creating a Master Package and call the other package (Child) from master package. Here we are not using any Script Task for that. We just use the Execute Package task. In this article I am not showing the process of transferring variable value from master package to child package. You can find this example from my previous blogs or any other blogs.

Most of the blog post that I see in the net to sequentially execute the package task they uses Script Task. But the specialty of this article is doing the same thing by using Execute Package Task in dynamic way. Hope it will be informative and you enjoy the session.

The Scenario

We have a Table objects named tbl_PackageInfo

idNo	PackageDtls	IsEnabled
1	E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-1.dtsx	1
2	E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-2.dtsx	1

This table contains the Package Path and isEnable columns. We want to Execute all the package mentioned in the table which have isEnabled = 1

Simple task, but need some trick to complete it. Hope you understand the Scenario.

How we Do that

We assume that the we have two Child package in the name of Child-1.dtsx and Child-2.dtsx

Step – 1 [ Table named tbl_PackageInfo and Insert the Package Information ]

IF OBJECT_ID(N'[dbo].[tbl_PackageInfo]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_PackageInfo];

   END

GO

CREATE TABLE [dbo].[tbl_PackageInfo]

  (

    idNo        INT           NOT NULL IDENTITY PRIMARY KEY,

    PackageDtls VARCHAR(1000) NOT NULL,

    IsEnabled   BIT           NOT NULL

  );

GO

INSERT [dbo].[tbl_PackageInfo]

   (PackageDtls, IsEnabled)

VALUES

('E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-1.dtsx', 1),

('E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-2.dtsx', 1);

Step – 2 [ The Entire Control Flow Task ]

Step – 3 [ The Variable ]

Step – 4 [ The Execute SQL Task ]

We need to execute those package which has IsEnabled is 1.

The SQL Statement

SELECT PackageDtls FROM [dbo].[tbl_PackageInfo] WHERE IsEnabled = 1;

Step – 5 [ ForEach Loop Containers ]

Step – 6 [ Execute Package Task ]

Here the trick is. In ForEach Loop containers we have to put the Execute Package Task. If we have two Package to execute, then we must put the two Execute Package task over there.

Now we configure the first Execute Package Task.

When we configure the first Execute Package Task the connection manager is automatically created. Rename it as Package Connection.

Right Click the Connection manager and select property and we have to configure the Connection property by Expression.

Now re-configure the entire Execute package task by choosing connection manager name in connection property.

Important Note:

If we take the single Execute Package task within the ForEach Loop Containers only the one child package is execute.

If we have more than one Child package, we must take Execute Package Task more then one depends on how many child package we have. Configuration of the entire child package is same. Just put the Connection manager name in the Connection property and Location property is File system.

Connection = < Name of the Connection Manger >

Location = File System

Step – 7 [ Observation ]

Summary

If you have any alternate process except Script Task, please mentioned in comments that we can share knowledge.

Hope you like it.

Posted by: MR. JOYDEEP DAS