Component Precedence

There are several ways to control the order of execution of deployed components.

– Create multistep Agent jobs
– Create parent packages
– Deploy to msdb & query sysssispackages to get package list.  Loop through packages in SSIS.
– Set a configuration value for each component’s precedence in the configuration table.  Use this value to set the order of components when looping in SSIS.

MSDB can be queryied to retrieve a list of deployed packages.

Transact-SQL

SELECT name   FROM [msdb].[dbo].[sysssispackages]   where folderid =    (    Select folderid    FROM [msdb].[dbo].[sysssispackagefolders]     where    foldername = 'Stage'   )

1 2 3 4 5 6 7 8 9

SELECT name   FROM [msdb].[dbo].[sysssispackages]   where folderid =   (    Select folderid    FROM [msdb].[dbo].[sysssispackagefolders]    where    foldername = 'Stage'   )

The below image shows a placeholder in the console.configuration table that can be set manually to define component execution precedence.  This value can be leverage in SSIS to set the order of execution when looping through packages.

Attached is a sample SSIS 2008R2 package with a simple loop task which executes a package based on the current package variable name.  The source query can be pointed to a list of packages retrieved from msdb or a list of packages from the configuration table.  If the configuration table is used, then ensure that the configuration filter string is trimmed to exclude the “-Precedence” portion.  This can be done in the sql statement such as below or in an expression within the loop package.

Transact-SQL

Substring([ConfigurationFilter], 1, LEN(ConfigurationFilter) - 11) as PackageName

1	Substring([ConfigurationFilter], 1, LEN(ConfigurationFilter) - 11) as PackageName