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.
