Home Forums SQL Server 2005 Business Intelligence What is the best way to make SSIS execute package task connections portable ? RE: What is the best way to make SSIS execute package task connections portable ?

  • Store the contents of the child packages in a table, say ETLChildPackages. Your master package would have a step that read the contents of this table (child package names) and stores the values in an SSIS variable of type object--you'll use the result as a table basically. Then you place your Execute Package Task inside of Foreach Loop Container. From here you're going to loop through the results of your variable of type object. Let's call this variable v_Packages.

    In order to accomplish this you'll use the Foreach ADO Enumerator option in the Foreach Task. In the Variable Mapping section of the Foreach task you would map a variable of type string, say v_PackageName, to the value in each row of your v_Packages, assuming you're only storing one "column" in v_Packages. If you are storing only one "column" in v_Packages then you'll map v_PackageName to Index 0.

    Where ever this package moves to you just make sure the packages are stored in MSDB and the ETLChildPackages table is created and populated as necessary.