Symptoms
This article describes how to create a shared package configuration file so that you can make configuration changes easily in Microsoft SQL Server 2008 Integration Services (SSIS).
Resolution
Description of a shared package configuration fileWhen you deploy sets of related SSIS packages, it is more efficient to reconfigure these packages by including shared settings. For example, a connection string might be shared across multiple packages to point to a frequently used database server. The connection string may have to be changed when the system is deployed from a test environment to a production environment or in other scenarios.
You can apply new settings to a package at runtime by using package configurations. Package configurations are created by using the Package Configurations tool in Business Intelligence Development Studio (BI Development Studio). By using the Package Configuration Organizer dialog box and the Package Configuration Wizard under Package Configurations, you can configure how XML and database configurations are applied to a package before it runs. Additionally, you can edit these configurations when the system is migrated. This mechanism is designed for use with individual packages. However, you can share a single package configuration file across multiple packages. This enables you to change package configurations in one place.
How to create a shared package configuration fileBefore you create a shared package configuration file, you must first uniformly design the set of packages. You must name every object consistently in the set of related packages that have to be configured together. For example, if you want the shared package configuration file to configure the ConnectionString property of a connection manager that is named “ReportsDB” in one package, every other package that shares this configuration file must also contain a ‘ReportsDB’ connection manager. If packages do not contain objects that are specified in the shared package configuration file, the packages cannot use the shared package configuration file. After you have verified that the packages contain consistent object names, you can create the shared package configuration file. To do this, follow these steps:In BI Development Studio, open the first package in Solution Explorer.In SSIS Designer, click the Package Explorer tab, and then on the SSIS menu, click Package Configurations.In the Package Configuration Organizer dialog box, click Enable package configurations, and then click Add.On the Welcome page of the Package Configuration Wizard, click Next.On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.On the Select Properties to Export page, select only the properties that you want to configure across multiple packages.
Note If the configuration type supports only one property, the title of this wizard page is Select Target Property.On the Completing the Wizard page, type the name of the shared package configuration file, and then click Finish.Test this first package to make sure that it works correctly.Open each additional package in BI Development Studio, and then in the Package Configuration Organizer dialog box, enable package configurations.Add the file that you created in step 7. When you are prompted to reuse or overwrite the existing file, click Reuse Existing.