Implementing a Data Warehouse with Microsoft SQL Server 2012: Dynamic Packages

Lesson 2: Package Configurations

In versions of SSIS before SQL Server 2012, you had to use package configurations to update properties, variable values, and connections at run time. You could have the package look to an external source for configuration information that changed the settings within the package when it executed. Package configurations are optional, but in real-life scenarios they are almost mandatory, because they provide a way for you to update package settings without having to open each package in Business Intelligence Development Studio (BIDS) or SSDT. For example, by using package configurations, you can maintain connection strings and variable settings for all of your packages in a single location.

Implementing Package Configurations

When you are executing a package in a package deployment model, the first action the package takes is to look at its configurations and overwrite the package’s current settings with the new settings from the configurations. Common elements that are configured by using package configurations are:

• Connection properties. These include properties that set the connection string, the server name, the user name, and the password.

• Package variable properties. You can set variable values, variable descriptions, and the Raise Change Event property.

• Package properties. These include any property on the package level, such as checkpoint and security settings.

Before you can enable package configuration, you must convert your SSIS project to the package deployment model. By default, a new package in the SQL Server 2012 version of SSIS is set up for the project deployment model. You can change this by selecting Convert To Package Deployment Model under the project’s name on the main toolbar. Note that you can convert only projects that do not have any parameters or project-level connection managers defined.

By default, each package has its package configuration turned off. To enable and set up configurations, you use the Package Configuration Organizer, with which you can perform the following tasks:

• Enable or disable a package’s package configurations

• Add or remove configurations assigned to the package

• Define the order in which the configurations are applied

To open the Package Configurations Organizer, open the package for which you want to turn on configurations, and then choose SSIS Configurations from the SSIS menu. To enable configurations, select the Enable Package Configurations check box at the top of the dialog box. Figure 9-10 shows the Package Configurations Organizer dialog box with package configurations enabled.

Creating a Configuration

To create a new configuration, click the Add button in the Package Configurations Organizer dialog box to start the Package Configuration Wizard. First you must specify the configuration type by selecting the appropriate value from the drop-down list, as shown in Figure 9-11. SSIS supports different package configuration types; Table 9-1 describes the configuration types you can use.

Table 9-1. Package Configuration Types

Type	Description
XML Configuration File	Stores configuration settings in an XML file in the file system. Use this option if you are comfortable working with configuration files and your project requirements let you store configuration information in a file system file. Note that you can store multiple configurations in a single XML file.
Environment Variable	Saves the configuration information inside the system’s global variables collection, which is called an environment variable. Only one property can be stored in each Environment Variable configuration.
Registry Entry	Lets you save package properties and settings in your computer’s registry.
Parent Package Variable	Provides a way for your package to inherit the value of a variable from a parent package. When a package is executed from another SSIS package by using the Execute Package task, the values of its variables are available to the child package through the Parent Package Variable configuration. With this configuration type, you can choose only one package property setting at a time.
SQL Server	Stores configuration settings in a SQL Server table. You can store multiple configurations in a single table.

Choose the most appropriate configuration for your environment and your project requirements. Ensure that you consider how the package will be supported in a production environment and how other technologies are supported and configured. Take care to evaluate any security and compliance requirements when you are storing connection information such as server name, user name, or password information.

The most commonly used configuration types are the XML Configuration File and SQL Server configurations. The next section looks more closely at each of these types.

Creating an XML File Configuration

When you choose the XML Configuration File type, you can specify the location for your configuration file. There are two ways to specify the location of the XML file:

• Enter the location of the file directly in the Configuration File Name text box. Use this when you intend to always use the same location and name for your configuration file.

• Use an environment variable that contains the location and name of the configuration file. To use this approach, you must create a system variable in your computer’s system properties. The value of the variable must contain the full path, name, and extension of the file.

  

  Using an environment variable for the file location pointer is called the indirect file location approach and is very valuable if your XML file location or file name might change in the future or already changes between environments. If you choose to use the environment variable, be sure to add it to the servers on which the package will run.

As with all of the configuration types, more than one package can use the same XML Configuration File. If you have several packages that have common properties, such as connection strings, you might want to have all of them use one XML file for configuration.

After you have defined the location and name of the file, you define the server settings and properties that the XML Configuration File should contain. Because these are common among all configuration types, this chapter reviews the SQL Configuration setup before describing the server settings and property definitions.

Creating a SQL Server Configuration

To store your package configurations in a SQL Server table, select SQL Server from the Configuration Type drop-down list in the Package Configuration Wizard. Using SQL Server as the storage mechanism for your configurations requires a different group of settings than those used by the other configuration types, such as XML Configuration File. Figure 9-12 shows the SQL Server configuration options available for setting up configurations.

Just as with the XML Configuration File type, you can specify an environment variable as the location of your configuration (for example, the data source name for the SQL Server configuration), or you can specify configuration settings directly. There are three settings that define the table location details:

• Connection. This must be a SQL Server–based connection that sets the server and database in which your configurations will be stored and read. If you did not define the connection you need, you can click New next to Connection to open the Configure OLE DB Connection Manager dialog box.

• Configuration Table. This is the name of the table in which the configurations will reside. This table has predefined column name and data type definitions that cannot be changed. To create the table, you click New next to the Configuration Table text box to open the Create Table dialog box, in which you can change the name of the table and execute the table creation statement for the connection that you specified in the previous setting.

• Configuration Filter. Multiple SQL Server configurations can share the same table, and you can specify the configuration you want by using the Configuration Filter drop-down list. You can enter a new filter or use an existing one. The name you select or enter for this property is used as a value in the Configuration Filter column in the underlying table.

Adding Properties to Your Configuration

No matter which SSIS configuration type you are using, you can select Properties To Export on the next page of the wizard to select the SSIS package and object properties that are to be used in the configuration. After you define the configuration type properties in the Package Configuration Wizard, click Next.

At this point, SSIS prompts you to verify whether configuration entries already exist for the configuration type you selected. If they do, SSIS prompts you to either reuse the configuration entries or overwrite them. If you see this dialog box, you will probably want to share the existing configurations between packages. If you do, click the Reuse Existing button. If you want to clear the existing entries and create new ones, click Overwrite.

If configuration entries do not already exist in this configuration, or if you clicked Overwrite, you will see the Select Properties To Export page, as shown in Figure 9-13.

The Select Properties To Export page uses a tree view structure of your package properties, allowing you to select the properties for the SSIS configuration you have selected. Properties are grouped within the following folders:

• Variables. Lists all of the package variables you can select for configuration entries, along with their properties.

• Connection Managers. Lists all of the package connections, from which you can choose the specific properties for your connections.

• Log Providers. Lets you dynamically set the log configuration.

• Properties. Displays a list of all package-level properties that you can use to configure your package.

• Executables. Contains the tree structure of your tasks and containers. By navigating through this tree, you can configure the specific properties of your tasks and containers.

If you are using an XML Configuration File, SQL Server, or Registry Entry configuration, you can set multiple configuration properties at one time by selecting multiple property check boxes.

Sharing, Ordering, and Editing Your Configurations

If you have several configurations in your list, you can define the order in which configurations are applied in a package. The configurations are called in the order in which they are listed in the Package Configuration Organizer. This is an important consideration if you have multiple configurations that will update the same property or if you have configurations that have a dependency on prior configurations. For example, you might have a configuration that updates a connection string, which is then used as the location of the configuration entries in a second configuration. Note that the last-applied property update will be the value that is used in the package.

A common approach is to share configurations between packages. If you do this, you might have configuration entries that apply to one package and not another. This does not affect package execution, but you will receive a warning to indicate that a configuration property does not exist in the package.

As a final note, you can modify all SSIS configuration entries you have made by simply editing the file, SQL Server, registry, or environment variable value. Look for the Configured Value property and change it as necessary.

PRACTICE: Using Package Configurations

In this practice, you will create an XML Configuration File and share it between two packages.

If you encounter a problem completing an exercise, you can install the completed projects from the Solution folder that is provided with the companion content for this chapter and lesson.

EXERCISE Create an XML Configuration

In this exercise, you use SSIS configurations to create an SSIS XML Configuration File that contains the connection string property of the AdventureWorks2012 and TK463 databases. You then share this configuration with another package.

1. Start SQL Server Data Tools, open the TK 463 Chapter 9 project, and open the FillStageTables_1.dtsx package.

2. Now you need to convert the project to a package deployment model. Click Project on the toolbar and select Convert To Project Deployment Model. Click OK in the dialog box.

3. In the Convert To Package Deployment Model dialog box, every step should show the Result value as Passed. Click OK to convert the project to a package deployment model.

4. Choose Package Configurations from the SSIS menu.

5. Select the Enable Package Configurations check box in the Package Configurations Organizer dialog box.

6. Click Add to create a new configuration.

7. Click Next on the Welcome To The Package Configuration Wizard page.

8. In the Configuration Type drop-down list, select XML Configuration File.

9. Click the Browse button next to the Configuration File Name box, browse to the \Chapter09\Lesson2\Starter\ installed files folder, and then type SSIS_Conn.dtsConfig. Click Save to save the file name and path.

10. Click Next in the Package Configuration Wizard to go to the Select Properties To Export page.

11. Under Objects, expand the Connection Managers folder, expand the Properties folder for the AdventureWorks2012 connection, and select the check box next to the ConnectionString property. Repeat the process for the TK463DW connection. Click Next.

12. Name the configuration MainXMLConfiguration and close the Configuration Organizer dialog box.

13. Save and close the FillStageTables_1.dtsx package.

14. Open the FillStageTables_2.dtsx package and repeat steps 4 through 9. Select the file you created in step 9, and click Next. You will be prompted to overwrite the existing file or reuse the configuration that it contains. Click the Reuse Existing button.

15. Name the configuration MainXMLConfiguration and close the Configuration Organizer dialog box.

16. Save and close the FillStageTables_2.dtsx package.

17. Execute the packages and try to change the XML file so that the connection string points to the TK463DWProd database created in the previous lesson, and execute the packages again.

Lesson Summary

• Package configurations are available in the package deployment model.

• Use package configurations if you are using previous versions of SSIS to set connection properties at run time.

• Use a combination of XML and SQL Server configurations to provide additional portability for your packages.

Lesson Review

Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

1. Which configuration types can you use to store configuration values? (Choose all that apply.)

   1. XML Configuration File

   2. SQL Server

   3. Any relational database system

   4. Registry entry

2. On which objects can you set dynamic properties by using package configurations? (Choose all that apply.)

   1. Parameters

   2. Variables

   3. Data flow transformations

   4. The Sequence Container task

3. Which SSIS elements can be configured by using a package configuration? (Choose all that apply.)

   1. Connection properties

   2. Package variable properties

   3. Parameter properties

   4. Package properties