How to execute child packages selectively based on

Question

I have an SSIS package that executes multiple child packages

pkg1
 |
 |
 |
pkg2
 |
 |
 |
pkg3

I would like to execution of the child packages configurable so that I can change which child packages to execute based on the configuration values.

Which option in SSIS would allow me to enable/disable the child package executions within the parent package?

Answer 1

You can put a Execute package task inside a Foreach loop container and control the packages to be run through a table. As you are planning to run the packages serially, this pattern will work well for you.

Some details:

1. Create a new table

   CREATE TABLE [dbo].[ActivePackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PackageName] varchar NOT NULL, [Active] [bit] NOT NULL ) ON [PRIMARY]

2. Insert two rows

   ID PackageName Active

   1 c_mp_Child1.dtsx 0

   2 c_mp_Child2.dtsx 1

3. Create a parent (p_MainPackage.dtsx) and two child packages (c_mp_Child1.dtsx and c_mp_Child2.dtsx)

4. Description of p_MainPackage.dtsx Variables

   sActivePackageFullPath (String)

   Expression: @[User::sSolutionFolder] + "\" + @[User::sActivePackage]

   Evaluate As Expression: True

   sSolutionFolder String

   oActivePackages Object System.Object

   sActivePackage String

5. Execute SQL Task -- Get list of active projects and store in oActivePackages

6. Foreach Loop Container

   Enumerator: Foreacj ADO Enumerator ADO object source variables: User::oActiveProject Variable mapping

: User::sActivePackageFullPath 6a. Inside: Execute Package Task Connection String for the Child package: @[User::sActivePackageFullPath]