Execution order of package configurations in SSIS

The following article tries to find the order of execution of package configurations. For this example, we will use SSIS 2012. We will create two package configurations - XML and SQL Server.

---

Package configurations in SSIS serve the purpose of making the package reusable across environments like Production, Testing and Development with no changes to package designed during Development Phase. 

In this example, we will create a single variable of type Int. We will create two different package configurations types and assign value to this variable. 

Package configurations are executed before the actual package content is executed. This means that the variable created above should have value from second configuration setting in the order. Let us confirm this. 

Create new SSIS project and use new package. 

Right click on the Control Flow are and select Variables. Create new variable of type Int32.

By default, SSIS 2012 is designed to work in Project Deployment Model and does not support Package Configurations.

To enable Package Configurations, right click on project under Solution Explorer and select Convert to Package Deployment Model. When prompted, click OK.

Now right click on Control Flow area and we should see Package Configurations. Select it to open Package Configuration Wizard.

Create new XML Configuration and select Variable property Value.

Create new SQL Server Configuration and select Variable property Value.

Now update the Value property in each configuration. For this example, under XML Configuration, Variable is set to 10 and under SQL Configuration it is set to 20.

 

Set required breakpoints.

Execute package and add Watch to check for Variable value. We should see that the SQL Server Configuration value is selected for this variable. This is because, we are setting the same variable first in XML and then in SQL. Since package configurations execute in the order of creation, the SQL setting for variable persists in the package.

Please feel free to correct me by commenting your feedback and suggestions.