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.
<?xml version=”1.0″ ?>
– <DTSConfiguration>
– <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy=”dinesh-PCdinesh” GeneratedFromPackageName=”LoadServices” GeneratedFromPackageID=”{F166287D-BEF4-4C9E-92F5-55F73305707D}” GeneratedDate=”12/07/2007 21:08:09” />
</DTSConfigurationHeading>
– <Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[InitialCatalog]” ValueType=”String“>
<ConfiguredValue>SSIS_PC</ConfiguredValue>
</Configuration>
– <Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[Name]” ValueType=”String“>
<ConfiguredValue>LocalHost.SSIS_Package_Config</ConfiguredValue>
</Configuration>
– <Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[Password]” ValueType=”String“>
<ConfiguredValue />
</Configuration>
– <Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[ServerName]” ValueType=”String“>
<ConfiguredValue>.</ConfiguredValue>
</Configuration>
– <Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[UserName]” ValueType=”String“>
<ConfiguredValue>sa</ConfiguredValue>
</Configuration>
</DTSConfiguration>
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.
<Configuration ConfiguredType=”Property” Path=”Package.Connections[LocalHost.SSIS_Package_Config].Properties[ConnectionString]” ValueType=”String“> <ConfiguredValue>Data Source=.;Initial Catalog=SSIS_PC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> </Configuration>
In case of the text file, the following will be the configuration file format.
<Configuration ConfiguredType=”Property” Path=”Package.Connections[ServiceCodesTxtFiles].Properties[ConnectionString]” ValueType=”String“> <ConfiguredValue>C:DataArticlesSSISPCSSIS_PC.txt</ConfiguredValue> </Configuration>
Apart from these configurations, which will satisfy our simple example, you can also pass a variable from package configurations as in the below example.
<Configuration ConfiguredType=”Property” Path=”Package.Variables[User::Setup].Properties[Value]” ValueType=”String“>
<ConfiguredValue>Development</ConfiguredValue>
</Configuration>
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_USERSSIC_PCDatabaseName 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.