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.
We have a Table objects named tbl_PackageInfo
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
DROP TABLE [dbo].[tbl_PackageInfo];
CREATE TABLE [dbo].[tbl_PackageInfo]
idNo INT NOT NULL IDENTITY PRIMARY KEY,
PackageDtls VARCHAR(1000) NOT NULL,
IsEnabled BIT NOT NULL
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.
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 ]
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