SQL Server Integration Services – Missing Package Configuration Option

SQL Server Integration Services – Missing Package Configuration Option

Hi,

Let’s dig more into SQL Server Integration Service and this time it is the package deployment model. With the plethora of changes that the SSIS team has come up with in the new release of SQL Server 2012, the package deployment is the one which is significant and requires more understanding.

For the first time when you open the SQL Server Business Intelligence Development Studio, you will notice many changes in the developer interface. If you see the graphic below:

The data sources, data source views are missing and a new entity – Project.params and Connection Managers are now made available. This new feature is part of SQL Server 2012 and the package if, deployed, will use the new Project Deployment Model and not the existing deployment mechanism. The scope of this post is not to highlight the advanced features in SSIS, but as the title suggests, enable package configurations in the SSIS.

While exploring the new features in SSIS, you might have encountered that Package Configurations is no longer visible. This option was available till SQL Server 2008R2 release, which the designer could access by two ways listed below;

• Right click on the control flow designer pane and then click on the Package Configurations
• Click on SSIS, the top menu item on the menu bar and then click on the Package Configurations

But, if you try to do the same in the SQL Server 2012, you might not find the Package Configuration option. This is shown in the graphic below.

This feature is still available and the package designer can very well use the package configurations.

The SSIS team has provided the designer with two strategies, namely

• The legacy deployment model
• The project deployment model

In short, the legacy deployment model supported package configuration to store configurable values, but if the package is deployed using the package deployment model, this features is no longer supported. The package now stores configurable values in the project parameters.

So, the designer must instruct or toggle the deployment model to use the legacy model. To simplify, this user action will convert the package to use the package configuration to store the configurable values. But this action will initiate a wizard which will prompt the user that all new features which are exclusive to SQL Server 2012 in SSIS will either be lost, or the user has to make the package compatible with the earlier release. For example, if the user created any parameters to parameterize any task, the package will fail the conversion and the user will have to remove the parameters, if any.

This action can be achieved by two ways:

• Right click on the Solution Explorer and then click on the Convert to Legacy Deployment Model
• Click on Project, the top menu item on the menu bar and then click on the Convert to Legacy Deployment Model

On successful, completion of conversion of deployment model, the package configuration will be made available to user; the look and feel of the designer pane looks similar to the earlier version of Business Intelligence Development Studio. The designer can now use the package configuration to store configurable values.

That’s all. Happy Learning!

Regards

Raunak Jhawar

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook