Dynamic Package Configuration - SQL Server 2008

The Package Configuration Organizer allows you to dynamically set package properties based on machine environment variables, XML configuration files, registry entries, a SQL Server table, or parent
package variables (sent by the calling the Execute Package task).

Setting configurations dynamically allows you to change important connections or settings without needing to hard-code them within the package. For example, if you have a development, staging, and production environment where you port your SSIS packages, you can use the Package Configuration Organizer to dynamically set the source server connections based on the local machine name environment variable. These settings are applied when your SSIS package is loaded (for example, if you add an existing package to a different project). Settings are not applied during execution time, unless the configuration is set through a parent package variable.

In this example, you’ll create a package configuration that can be used to update the SQL Server instance and database based on an XML file.

  1. In BIDS, create a new SSIS project called PackageConfigExample.
  2. In the default Package.dtsx package, create a new data connection to the SQL Server AdventureWorks database.
  3. Right-click the Control Flow design surface and select Package Configurations.
  4. In the Package Configurations Organizer dialog box, select Enable Package Configurations.
  5. Click the Add button to add a new configuration.
  6. Click Next on the Package Configuration Wizard welcome screen.
  7. On the Select Configuration Type screen, for the configuration type, select XML Configuration File, as shown in Figure. Keep “Specify configuration settings directly” selected. This means the configuration will expect the XML file to be in the same file location wherever the package is loaded. Use the other option, “Configuration location is stored in an environment variable,” if you expect this location to change. For the configuration filename, type C:Apress est.dtsConfig. A new file will be created if one does not already exist. Click Next to continue.
  8. Selecting the configuration type in the Package Configuration Wizard

    Selecting the configuration type in the Package Configuration Wizard

  9. On the Select Properties to Export screen, under the PackageConnections folder, expand your SQL Server instance connection. Expand the Properties folder, check the Connection- String, InitialCatalog, and ServerName properties. Then click Next.
  10. Selecting properties to export in the Package Configuration Wizard

    Selecting properties to export in the Package Configuration Wizard

  11. On the Completing the Wizard screen, type SQL Server Connection Change in the Configuration. Name text box. Click Finish.
  12. Your new configuration will now appear in the Package Configuration Organizer. Click Close.

Open the configuration file from the location you specified. Here, you can change the three property values. As shown in the following code, the ConfiguredValue tags outline the values for each configurable property. If you change the property in the XML file and then load the package to a new project, the SSIS package will inherit the properties based on the XML file.

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo
GeneratedBy="JOEPRODOwner" GeneratedFromPackageName="Package"
GeneratedFromPackageID=
"{FA099B09-C230-4688-AF93-E88C73C8683F}" GeneratedDate="1/1/2005 9:28:29 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property"
Path="Package.Connections[{C2FB43E4-2DD7-44D0-B616-
D9EF80D60901}].ConnectionString" ValueType="String">
<ConfiguredValue>Data Source=localhost;Initial Catalog=AdventureWorks;
Provider=SQLNCLI10.1;Integrated Security=SSPI;
Auto Translate=False;</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections
[{C2FB43E4-2DD7-44D0-B616-D9EF80D60901}].InitialCatalog" ValueType="String">
<ConfiguredValue>AdventureWorks</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path=
"Package.Connections[{C2FB43E4-2DD7-44D0-B616-D9EF80D60901}].ServerName"
ValueType="String">
<ConfiguredValue>localhost</ConfiguredValue>
</Configuration>
</DTSConfiguration>

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics