RSS Feed (xml)
Introduction
If you are working on SQL Server Integration Service (SSIS) packages, it is highly unlikely that you are working in the production environment directly. Alternatively, you might be working in the development environment and later deploying to the production environment. Apart from these environments, you normally have testing and staging environments as well. For all these cases you probably have different servers and folders to work with.
Apart from different environments, sometimes, there are changes made by the client. Sometimes the client may change the drive from C to D, or change the database name or configuration. In case of SMTP mail servers, you may have to change the server IP and authentication when the environment changes.
So when the environment changes, you may have to change all the configuration settings associated with SSIS packages. You can imagine the headache when you have to change all the packages. Believe me, it is not an easy task and what if you miss one? You know the consequences.
To illustrate this let me start with simple example. Let us say we want to populate SQL Server Database from a text file.
Text file may be in D:\Textfile folder and SQL Server may have SQL Server authentication. In this case I will write a simple dtsx to import data. Then we need to transfer this to the production environment. (Let us forget about test and staging environments for simplicity). Production environment may not have D drive to keep files, instead it may have an E drive and SQL Server may be set to windows authentication. So we have to make those changes again. You can imagine the work load and the associated risks when there are 1000+ packages.
Package Configuration in SQL Server 2005
In SQL Server 2005, you have an option called Package Configurations under the SSIS menu option to allow database developers to store package configurations outside the SSIS packages.
I will run through this feature by an example.
Below is the format of the table which will store the data imported from the text file.
CREATE TABLE [dbo].[SSServices](
[ID] [int] IDENTITY (1,1) ,
[Name] [varchar](50) NULL,
[ServiceCode] [varchar](50) NULL
) ON [PRIMARY]
Next, create a simple SSIS to load the text file data into the created table. Creating the SSIS will not be discussed as it will deviate from the main topic. The SSIS package looks like following.
After creating the SSIS Package, the next step is to save the package configurations outside the package. First we have to include SQL Server credentials and the database name and then we have to include the text file path for text file.
If you click the Package Configurations option, first you have to enable the package configurations option which will be in the next screen you will get after the Welcome screen. In that dialog, click the Add button and you will be taken to the following screen.
As you can see there are few configuration types available with package configurations. They are - XML configuration file, Environment variable, Registry entry, Parent package variable and SQL Server. We will consider each option so that you can select perfect option for your requirements.
After selecting a relevant option, the next step is to select necessary properties that you want save as package configurations.
For this example, I will select ‘XML Configuration file’ and in that case you need to provide the XML file location and file name.
As we then need to select database settings, we have to select LocalHost.SSIS_Package_Config connection manager which is the connection manager for the SQL Server database. Then you will need to select the properties you require to save as a package configuration from the following screen.
In this case, you can either select entire ConnectionString option or you can select ServerName, UserName, InitialCatalog to construct the connection string. The latter is the best choice as it will give you more options when there is a change.
For text file configurations, you can select the Load Txt file and in that you can select the ConnectionString and click next. Following screen will appear.
That is all you have to do for this example. The next time you load the package, your package will read the configurations from an XML file. You can verify this by changing the XML file contents and reloading the package. After reloading the package, view the connection manager properties and you can see that the connection manager properties are updated from the values in the XML files are updated for the properties.
Even though we have completed the example let us move further ahead and observe the other options available with Package Configurations.
From the above image, you can see that almost all the properties of the object that exist in your package can be saved in package configurations. However, most of the time package configurations are used to save connection settings and variables as those are the most used configurations.
Just for the comparison, the following image shows what you get for SQL Server connection property window from Visual Studio and SSIS package configurations window.
From the above image you can see that DataSourceID, DelayValidation, Expression are the missing properties in package configurations.
XML Configuration File
If you prefer to select your package configuration in an XML file, it will be saved in an xml file which has .dtsconfig as his extension by default.
Following will be the XML format for configurations for database server which has SQL Server authentications.
-
-
-
-
-
-
-
You can see that password is not saved which is the default behaviour for security reasons. If you want to save password explicitly, you can edit this configuration file. Apart from the configuration values, it will contain the generic information like when, where and who created this package configuration which will also be useful.
If your SQL Server has windows authentication, your configuration file will look like following.
In case of the text file, the following will be the configuration file format.
Apart from these configurations, which will satisfy our simple example, you can also pass a variable from package configurations as in the below example.
The above configurations will change the value of the Setup variable which is in the package to Development. So you can therefore change the default or initial value of the package variables, without opening the package.
Now back to the example again. After creating those two configurations, the following screen will be shown.
Priority buttons are needed when you have same configuration in multiple files. Higher priority configuration values will be loaded. However, it is advisable not to have same configurations across multiple configurations. Having same configurations in several files may lead to many mistakes.
Environment Variables
After selecting the ‘Environment variable’ configuration type from the Select Configuration Type dialog, you will be asked to enter Environment Variable name. Please note that selecting this option will not create an environment variable and it's values for you. So, you need to create an environment variable and set the necessary values for it.
Another problem with this method is you have to assign one environment variable for each property unlike in XML configuration where we can save many settings in one single XML file. For example, to save Database configurations you need to have one Environment variable for each database name, server name, user name and password where we required only one file in case of XML configurations.
Also it is worth remembering that you have the option of setting the environment variable either as a System Environment or User Environment variable. If you saved it as a System Environment variable, make sure to restart the server after setting or changing the environment variable value. Until you do a restart, it will not be in effect. However, you will not have this issue if you save them in user environment variables, as user environment variables will take into effect soon after they are changed. In that case you need to assign environment variables for each user.
Registry Entry
Another common way of keeping your package configurations is saving them in the Windows Registry. However, you have to store your data in HKEY_CURRENT_USER of the registry. You can’t have your settings in HKEY_LOCAL_MACHINE. This means that for each user, you will need to define your package configurations like in User Environment.
Unlike XML configuration, just selecting the registry option won’t create registry entries for you. You have to manually create your registry entries.
In addition, you can’t have your own string values for registry. Your string value should be named as Value. For example, if you are looking to store database name of your package, you need to create a registry entry like following image.
In the above example SimpleDB is the name of the database name stored in HKEY_CURRENT_USER\SSIC_PC\DatabaseName key value and Value string name. When you are entering this registry value in the package configuration, you should enter as below.
This means that you have to remove HKEY_CURRENT_USER and Value from the Registry key entry.
SQL Server
Why save data in outside SQL Server? Why not save it in SQL Server itself. Yes it is possible and it is simple too. Configuration values will be saved in a SQL Server table which should have following table format.
CREATE TABLE [dbo].[SSIS Configurations]
(
ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL
)
The following are the parameters you need to supply for the SQL Server configurations.
Configuration filter is a label which all the configurations will be created with. SQL Server has a few advantages over the other configurations. Configured value fields contain the value of the property. PackagePath saves the entire path to the object property. For example, LocaHost.SSIS_Package_config connection user name property will be saved as \Package.Connections[LocalHost.SSIS_Package_Config].Properties[UserName]. ConfguredValueType is the data type of the property. Unlike Registry and Environment variable configuration, SQL Server configurations will create the values for you.
The below image shows your set of values that will be saved in the SQL Server table named in SSIS Configurations.
Again as XML, you need to change password value of the column.
An important consideration is security on configuration settings. As these settings may contain user credentials, SQL Server is the best place to keep them as SQL Server provides more security out-of-box. Another advantage that you get from SQL Server is that your configurations are easy to maintain. You can do so by using T-SQL commands such as SELECT, UPDATE, DELETE.
Then there is the obvious question. Where are we going to save configurations of that configuration database? This database should be a predefined database. This is the only issue that will arise when you saving your package configurations in SQL Server.
Apart from above four columns, it would have been much better if there are additional columns to store when and where this configuration was created as in the XML configurations.
Parent Package Variable
In complex situations, you might have several levels of packages. For example Package A will call Package B and Package B will call package C and D. If you have packages which take your values like text files path other settings, it is advisable to load it to the main package (Package A) and then pass it to others. In this design method, you have the luxury of changing configuration only in main package so that it will pass to other packages.
In Parent package configurations, you need to provide current package variables and parent package variables which you need to retrieve values.
Storing Package Location
If you look closely you can observe that there is an option to save the location of the package configurations. For example, you can save the location of the XML file in a Environment variable, so that it is only matter of changing the value of the Environment variable. However, all the issues applied of environment variables apply here too.
Important Points
If you have an SSIS Package with loading configuration settings, sometimes you may receive the following error. Incidentally this is a very frequent question in the user forums.
Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
The message is only raised when the package contains the actual protected data such as passwords and connection strings that can't be decrypted. If the package has ‘EncryptSensitiveWithUserKey’ as ProtectionLevel, but does not have any sensitive data, you can change this to DontSaveSensitive so that you will not see this message.
By default SSIS packages have an ‘EncryptSensitiveWithUserKey’ setting for the ProtectionLevel. As you are providing passwords in package configurations, it is not encrypted so it should be changed 'DontSaveSensitive' . You can change this by selecting property option of the packages.
Best Practices
It is always best to assign one setting each configuration. For example, in our example of loading a text file to SQL Server database, we could have easily configured one XML config file to load database connections and text file path. If you select more than one configuration, it will reduce the usability and will increase the risk of errors. If there are numerous configurations in one setting, you have to pay attention to the priorities which can be difficult.
Your settings are going to contain the database user credentials which raises security concerns. In that case you have to take precautions that unauthorized users should not be able to access the database credentials. In this case, SQL Server configuration is ideal as you can use the SQL Server security model. However, if you decide to store them in XML files you have to make sure that those XML files are kept in a secured folder so that other users do not have access to them.
Suggestion to Microsoft
No doubt this is a fascinating feature in SQL Server Integration Services. However, I have one question, why this feature is not included for other members of the Microsoft SQL Server Business Intelligence family such as SQL Server Analysis Server (SSAS) and SQL Server Reporting Services (SSRS). It would have been much better if this feature is available at least for data sources of SSAS and SSRS.
Conclusion
Package configuration is one the great features available with SQL Server 2005. However, before selecting appropriate option carefully consider all the possibilities.
No comments:
Post a Comment