0

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?

Angad
  • 1,144
  • 3
  • 18
  • 39
  • On precedence constraint i added expression to check if the variable is set to true. But when it is set to false it just stops and doesnt go to the next task. – Angad Feb 06 '13 at 16:16

1 Answers1

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]

Anoop Verma
  • 1,495
  • 14
  • 19