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
nice .. thank you
ReplyDelete