Package Configuration Types
| Integration Services supports several different types of Package Configuration types. Each type has its strengths and you'll likely find one that is your favorite and perhaps works better given the different requirements in your space. XML configurations are useful when moving packages or when you just prefer working with files and the file system. SQL Package configurations are ideally suited to large production systems with multiple servers and packages that you need to manage centrally in an enterprise environment with elevated security requirements. Environment Variable configurations are useful for describing a machine to packages and simplifying package migration. Parent Package configurations are useful when you need to modify property values on objects in the child package. Registry configurations provide a simple solution similar to environment variables but are more secure. The variety of Package Configurations types provides a lot of flexibility for how to approach the package deployment problems mentioned earlier and discussed in the following sections. Depending on your requirements, you can use one type exclusively or mix them in various ways. The following discussion describes each of the configuration types in detail. Tip When using configurations, it can be difficult to know if the configuration failed just by looking at the package. Depending on the type of the property that will be configured, it's a good idea to give the property a well-known default value so that when the configuration fails, it can be detected. Configurations do emit a warning if they fail, but that doesn't help if the configuration is accidentally deleted. So, it's a good practice to give configured properties and variables a value that you will recognize. For example, for string properties, set the value to Default Value. Then, if you see that property anywhere in the package, you'll have a pretty good idea that it was supposed to be configured and wasn't. For numeric property values, use the value 42, because, after all, 6 x 9 = 42, which is the ultimate default value, at least according to Douglas Adams. XML Configuration FileXML configurations are stored in files with the default extension .dtsconfig and can configure more than one property in a package. XML configurations are structured as follows: <?xml version="1.0" ?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="Kirk Haselden" GeneratedFromPackageName="Configurations" GeneratedFromPackage GeneratedDate="12/24/2006 12:22:01 PM" /> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Variables[SAMPLES::SERVERNAME].Properties[Value]" ValueType="String"> <ConfiguredValue>ROMEO</ConfiguredValue> </Configuration> </DTSConfiguration> There are two main sections in the XML configuration file shown in bold print in the preceding sample.
When the Integration Services runtime loads an XML configuration, it completely ignores all data in the heading and applies each configuration entry independently in the order found in the file. Note XML configuration files have the .dtsconfig extension by default, but can have any extension, including .xml or none at all and still be valid. Package PathThe package path is stored in the Path attribute of the Configuration node. In the preceding example, the package path is \Package.Variables[SAMPLES::SERVERNAME].Properties[Value], which sets the value of the SERVERNAME variable. ValueThe value is found in the ConfiguredValue node; in the preceding example, the value is the name of the server, ROMEO. ValueTypeThe ValueType specifies the type of the value in the Value node. The package uses the ValueType as an aid to convert the value from a string to the correct target type. If it fails to convert the type, you receive a warning similar to the following: Warning loading Package1.dtsx: Failure importing configuration file: "Package.dtsConfig". The warning is provided to let you know that the configuration failed, but does not affect the outcome of any other configurations the package might have. All configurations that are applied before and after a failed configuration will continue to be valid even if one configuration fails. ConfiguredTypeThe ConfiguredType specifies the type of the property to be configured. It is for documentation purposes only. Caution Although the ConfiguredType is for documentation only, if you remove the attribute from the Configuration node, the configuration is invalid and any package attempting to use the configuration fails to load. The Attribute value can be empty or even contain comments about the configuration if you want, but it must remain intact. Configurations EditorIntegration Services provides no way to directly edit XML configurations short of opening the configuration in an XML editor or Notepad or opening the Package Configurations Organizer. The problem with using the Package Configurations Organizer is that it depends on the package to generate the configuration and can be cumbersome. Occasionally, it would be nice to open an XML configuration and modify it in a simple way without worrying about corrupting the file or opening the development environment. A simple utility for editing configurations accompanies this book. It currently only supports XML configurations, but could be expanded to support other types as well. You can find it in the samples under the ConfigEdit subfolder. Figure 14.3 shows the editor after loading a configuration. Figure 14.3. The Configurations Editor for viewing and editing XML configurations You can create a new configuration or edit existing ones. The utility does some lightweight validation on the configuration and alerts you to some common problems with configurations. This utility is perhaps most useful when you need to make small changes or tweaks to existing configurations without loading the designer or if you just want to clearly see what values a configuration changes. To set up the Configurations Editor utility, go to the Utilities\ConfigEdit subfolder of the Samples folder. Run SetupConfigEdit.exe and it installs the needed components. You can uninstall it from the Add/Remove Programs Control Panel or by running SetupConfgEdit.exe again. Environment VariableEnvironment Variable configurations have two parts: the package path, which is stored in the configuration object's ConfigString property as described previously, and the Value, which the environment variable contains. When the package evaluates the configuration object, it retrieves the ConfigurationType, determines that it is an Environment Variable configuration, and then retrieves the environment variable name from the ConfigString. Then, it retrieves the value from the environment variable. Finally, it retrieves the package path from the ConfigurationVariable property and applies the value to the property. Setting Up Environment VariablesEnvironment variables have been around for a long time. Some have commented that they're such an old technology, why would Microsoft build a new feature on them? Well, as it happens, they're just so darned useful. But, on occasion, they can be tricky. So, here's how to set up an environment variable that you can later reference in an Environment Variable configuration.
Caution Environment variables are process specific and inherited from the parent process. Therefore, when you launch the designer, it will have inherited its environment variables from the process that launched it. Even if you change the environment variables in Control Panel or in another command window, the changes are not available to the designer until you shut it down and restart it. If you create an environment variable through Control Panel as described, it is always available on that machine until you remove it through Control Panel. If you create an environment variable programmatically or through the command line, the environment variable is only available to the process that created it. Setting Up an Environment Variable ConfigurationIf you already have an environment variable set up as described previously, you can select that type in the drop down and click the Next button to get to the Select Configuration Type dialog box, as shown in Figure 14.5. This is probably the easiest configuration to set up because you need only specify the environment variable. The sample packages that ship with this book use Environment Variable configurations. Figure 14.5 shows the SSISSAMPLESERVER environment variable, which the sample packages use in connection strings when accessing the AdventureWorks sample database. Figure 14.5. Creating an Environment Variable configuration
After you've selected the environment variable to use, you need to specify the property that you want to configure and you do that in the Select Target Property dialog box, as shown in Figure 14.6. Figure 14.6. Specifying the property to configure
This is where you build the package path. When you select the property to configure, the wizard builds the package path and stores it in the new configuration. This process is essentially the same for all Package Configuration types with one exception. For multivalue configurations such as XML and SQL Server, the wizard allows you to select more than one property to configure. Note You might notice there is no ValueType specified for Environment Variable configurations. The IS runtime successfully converts the value in an Environment Variable configuration to the type of a property (if it can be converted) when configuring properties. It gets tricky when you configure variables. Because variable types change depending on the type of value, when the IS runtime applies the Environment configuration, the variable contains the string "42. " However, if you configure an Int32 type property with the same Environment Variable configuration, the IS runtime correctly converts the value to an integer. The configuration information is stored in the following places:
Registry EntryRegistry configurations are fairly straightforward. The Registry configuration looks for a string value with the name Value in the key that you specify in the configuration. So, for a value stored in the HKEY_CURRENT_USER\AConfig\Config1\Value key, you would specify AConfig\Config1 for the ConfigString. This is also the same string you would type into the Package Configuration Wizard. The HKCU key and the Value string are assumed. You can place these configurations anywhere in the Current User tree and you can create multiple keys under the same tree, as shown in Figure 14.7. Figure 14.7. Registry configurations are stored in the Value string value Note It wasn't Microsoft's intention to make Registry configurations undocumented or difficult to use. They were written very early in the product cycle and seem to have slipped through the documentation cracks unintentionally. Still, after you know the format, it's pretty easy to set them up. Figure 14.8 shows the Package Configuration Wizard with the correct settings for a Registry configuration stored in the previously mentioned key. The simplest way to set these up is to copy the key in regedit and paste it into the Registry Entry field, deleting the Value and HKCU portions. Figure 14.8. The Registry settings in the Package Configuration Wizard
For indirect Registry configurations, provide the name of the environment variable that contains the Registry key configuration string, as described previously. Parent Package VariableParent Package Variable configurations provide a way to set a property or variable value in a child package from the value of a variable in the parent package that executes the child. Parent Package Variable configurations use the pull model, which means that you set up the configuration on the child to pull the value from the variable in the parent package. This is useful for situations when you want a child package to be controlled or influenced by the executing parent package. Note Because child packages inherit parent package variables, some have questioned the need for Parent Package Variable configurations. The logic being that because the child package can see the parent package variables already, the child package can directly reference the parent's variables. There are a few problems with this assumption. First, during design time, the package does not have access to the parent package variables. Only those tasks that do late variable access by name or GUID ID such as the Script Task can reference parent package variables. Second, child packages that reference parent package variables directly will fail if run independently. If the child package will always be executed by the same parent package, or you're sure that the parent package will always have the correct variable value, you might not need a configuration. However, some tasks require a variable to be available when validating. Because child packages don't have access to the parent package variables when validating or at design time, these tasks fail to validate. Parent package variables allow you to create your package as a standalone package with a default value stored in a variable or property in the child package, while allowing the variable or property to be configured from the parent package.
Figure 14.9 shows the Package Configuration Wizard settings for creating a Parent Package Variable configuration. Figure 14.9. Creating a Parent Package Variable configuration
For indirect Parent Package configurations, the environment variable should contain the name of the parent variable. SQL Server TableSQL Server Table configurations store the configurations in a SQL Server table and can include multiple configurations per package. The table can reside in any SQL Server database, have any valid SQL Server table name, and must be defined as follows: CREATE TABLE [TABLE NAME] ( ConfiguredValue NVARCHAR(255) NULL, PackagePath NVARCHAR(255) NOT NULL, ConfiguredValueType NVARCHAR(20), ConfigurationFilter NVARCHAR(255) NOT NULL ) The ConfiguredValue column specifies the value to apply to the property or variable value. The PackagePath column contains the package path, of course. The ConfiguredValueType specifies the type of the value as a hint to the SSIS runtime for converting to the correct type when applying the configuration. The ConfigurationFilter is how you differentiate between configurations. You might have configurations for all the packages in your solution stored in the table, but only want to apply certain configurations to certain packages. The ConfigurationFilter column allows you to specify a value that the SSIS runtime uses in a SELECT clause. If you view a package with a SQL Server Table Configuration in the Code Editor, you should see something similar to the following abbreviated Configuration node: <DTS:Configuration> <DTS:Property DTS:Name="ConfigurationType"> 7 </DTS:Property> <DTS:Property DTS:Name="ConfigString"> ".\YUKONB3.ISSAMPLES";"[dbo].[SSIS Configurations]";"SAMPLES"; </DTS:Property> </DTS:Configuration> The important thing to note here is the format of the configuration string. The first section of the configuration string is the name of the connection manager that the configuration will use to connect to the SQL Server. The connection manager must be defined in the same package. The second section is the table name. Because SQL Server Table configurations can use any table with any name having the correct schema, the configuration requires the name of the table where the configurations are stored. Because of this, it is possible to have more than one configurations table in a server. The last section is the query string or filter. This is the string the SQL Server Table configuration uses in the SELECT clause to filter for the desired configurations. If you need to use a semicolon in one of the values, use double quotes around the sections, as shown in the preceding example. Figure 14.10 shows the configuration string in the Package Configurations Organizer. Figure 14.10. The configuration string contains the name of the connection manager, the configurations table name, and the configuration Filter string Figure 14.11 shows the same SQL Server Table configuration in the Package Configuration Wizard. Figure 14.11. A SQL Server Table configuration in the Package Configuration Wizard
Notice the Connection, Configuration Table, and Configuration Filter fields correspond to the sections of the configuration string in the package XML in Code View. Finally, the ConfigString has the same format for indirect SQL Server Table configurations. The environment variable would have the format <connection name>;<table name>;<filter>. Tip You might notice that SQL Server Package configurations use configurable objects, namely connection managers. If you are clever, you might notice that it is possible to configure SQL Server Package configurations by configuring the connection manager that they use. Package Configurations SummaryFigure 14.12 shows a diagram of package configurations. The solid lines represent package configurations retrieving settings from the respective sources. The annotations on the solid lines describe what the ConfigString or Indirect Environment Variable configuration contains. The dashed lines represent the values being applied to the destination property or variable value. Notice that XML and SQL Server Table configuration types can configure more than one property or variable value per configuration and that Parent Package configurations pull the value from a variable in the parent package. Figure 14.12. A diagram of package configurations Caution I sometimes receive email or see posts on the SSIS forum from people who are receiving configuration warnings and believe that the warnings are causing their packages to fail loading or executing. Configurations only emit warnings and should never raise an error. Errors cause packages to fail. Often, the packages fail to execute because the configuration fails to configure some critical property that makes the package functional. In any case, the warnings are emitted to alert you to the configuration failure, but they do not fail the package execution and are not fatal. Tip Packages have a property called SuppressConfigurationWarnings that filters out all warnings caused by package configuration failures. If you really don't want to see configuration warnings, you can set this property to trUE and they will all be hidden. Although useful when developing packages, this filter should never be enabled in packages on production machines.
|
EAN: 2147483647
Pages: 200