SSIS Calling the Child Package

Introduction

Subroutine is an important part of the workflow. By using Executing Package Task control flow task we can use the existing package as a part of our SSIS workflow. It reduce the coding and easier for maintenance.

Use of Execute Package Task

As per MSDN

Breaking down complex package workflow:

This task lets us break down workflow into multiple packages, which are easier to read, test, and maintain.

Reusing parts of packages:

Other packages can reuse parts of a package workflow. For example, we can build a data extraction module that can be called from different packages. Each package that calls the extraction module can perform different data scrubbing, filtering, or aggregation operations.

Grouping work units:

Units of work can be encapsulated into separate packages and joined as transactional components to the workflow of a parent package. For example, the parent package runs the accessory packages, and based on the success or failure of the accessory packages, the parent package either commits or rolls back the transaction.

Controlling package security:

Package authors require access to only a part of a multi package solution. By separating a package into multiple packages, we can provide a greater level of security, because we can grant an author access to only the relevant packages.

Case Study

Here we are taking a simple case study to understand the process.

We have two package called Parent Package and Child Package. The Parent Package read a table objects called tblEmployee_Records and search for Grade-A holder employee. If he fined the records then it called the child package and stores the records in another table objects called tblGradeA_Employee_Records.

Here the parent package supplied the Grade of "A" to the Child Package and the child Package retrieve all the records of Grade-A and store it in destination table. 

As it is a simple demonstration, Limitation of this case study is only one employee of each grade can exists on the source table objects.

How we configure that

Step-1 [ Creating Source and Destination Table Object ]

-- Source Table Objects

IF OBJECT_ID(N'tblEmployee_Records', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tblEmployee_Records;

   END

GO

CREATE TABLE tblEmployee_Records

       (EMPID     INT               NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EMPNAME   VARCHAR(50)   NOT NULL,

        EMPGRADE  VARCHAR(7)    NOT NULL);

-- Inserting Records

INSERT INTO  tblEmployee_Records

       (EMPNAME, EMPGRADE)

VALUES ('joydeep Das', 'A'),

       ('Sangram Jit', 'B'),

       ('Debayan Bishwas', 'C'),

       ('Koushik Acharya', 'D');

-- Listing Reords

SELECT * FROM tblEmployee_Records; 

EMPID	EMPNAME	EMPGRADE
1	joydeep Das	A
2	Sangram Jit	B
3	Debayan Bishwas	C
4	Koushik Acharya	D

-- Destination Table Objects

IF OBJECT_ID(N'tblGradeA_Employee_Records', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tblGradeA_Employee_Records;

   END

GO

CREATE TABLE tblGradeA_Employee_Records

       (EMPID     INT           NOT NULL PRIMARY KEY,

        EMPNAME   VARCHAR(50)   NOT NULL,

        EMPGRADE  VARCHAR(7)    NOT NULL);

Step-2 [ Control Flow and Data Flow tasks of Parent and Child Package ]

Step-3 [ Declaring Variables of Parent and Child Package ]

Step-4 [ Execute SQL Task Editor Configuration ]

First we define the Connection and in the SQL Statement we provide SQL string like this.

SELECT CONVERT(varchar(7), EMPGRADE)EMPGRADE

FROM tblEmployee_Records;

Step-5 [ Foreach Loop Editor Configuration ]

Step-6 [ Execute SQL Tasks within the Foreach Loop Container Configuration ]

First we define the Connection and in the SQL Statement we provide SQL string like this.

SELECT CONVERT(VARCHAR(7), EMPGRADE) AS EMPGRADE   

FROM   tblEmployee_Records

WHERE  EMPGRADE = ?

Resultset : Single Row

Step-7 [ Parent Package Precedence Constraint Editor Configuration ]

Step-8 [ Execute Package Task Editor Configuration ]

Step-9 [ Package Configuration Organizer – Child Package ]

Step-10 [ Child Package – OLE DB Source and OLE DB Destination ]

This configuration is as before. If have you any confusion, please refer to my previous article.

Source OLE DB Editor Configuration

Step-11 [ Run the Package – Parent Package ]

Step-12 [ Analyze the Destination Table Objects ]

SELECT * FROM tblGradeA_Employee_Records   

EMPID                  EMPNAME                      EMPGRADE

1                              joydeep Das                       A

Hope you like it.

Posted by: MR. JOYDEEP DAS