Using indirect configuration with SSIS

Using indirect configuration with SSIS

SSIS has a great feature called indirect configuration. It is used to store the configuration access path into the computer’s environment variable. This feature greatly facilitates the deployment of an SSIS solution. All you have to do is to set the environment variable on the target computer and all packages in the solution automatically points to the right place.

Configurations are at the very heart of SSIS packages. They are used to set specific properties to packages objects. 5 configuration types are available within an SSIS package:

1. XML configuration file: an XML file (.dtsconfig) is created in the folder you specify.
2. Environment Variable: The property of the package object is set to the value of the chosen environment variable.
3. Registry Entry: The property of the package object is set to the value from a registry.
4. Parent package variable: Used to pass parameters from a parent package variable child package object property.
5. SQL Server Used to initialize a package objects property from a SQL Server configuration table (this will be explained in a subsequent post).

All of these configuration types, except one, have another option button at the bottom of the package configuration wizard window “Configuration location is stored in an environment variable”। This enable to refer to the configuration file(।dtsconfig), registry entry, parent package variable or SQL Server table connection string using the value of an environment variable. The only exception in the list is the Environment variable configuration: since we already specify that the package property object is set via an environment variable, it is then already using indirect configuration.

A practical example on using indirect configurations
The best way to demonstrate the indirect configuration usage is to build an example। Start business intelligence development studio (bids) and choose “Integration Services Project” from the New project templates। Give a name to the project and if rename “Package.dtsx” in the solution explorer to “Indirect configuration example”. Click “Yes” when asked to rename the package object as well. The last action causes the package name to be renamed as well as the package file name.

Next, create a connection manager: in the connection manager’s window at the bottom of the package window, right-click into it and choose “New OleDB connection”. Click New, the connection manager window appears:

Choose your server from the “Server name” combo box. Make sure that “Use Windows Authentication” radio button is selected then choose “AdventureWorks” database from the “Select or enter a database name” combo box. Click then on “Test Connection” and a message box appears confirming that the test connection succeeded. Rename the new connection manager “cmgr_AdventureWorks”. The prefix is part of a naming convention I use. Having a naming convention is very useful because it helps to categorize different package’s objects and it makes it easier to parse a log file when, for example, a load is running or has crashed.

On the control flow of a package, select SSIS in the menu bar then choose “Package configurations”. The package configuration appears. Check the checkbox “Enable package configuration” then, click “Add” at the bottom of the window. The “Package Configuration Wizard” window appears

From the “Select Configuration Type” page, choose “XML configuration file”. Type cfgn_AdventureWorks in the “Configuration file name” textbox. Click on the “browse” and then the “Save” buttons. This will create a XML configuration file named cfgn_AdventureWorks.dtsConfig in the project’s folder. Click on the “Next” button.

The next wizard’s page “Select Properties to Export” enables us to export (store) multiple properties values into the configuration file. Since we only want to set the connection string of our connection manager, in the treeview at left, expand Connection managers, cmgr_AdventureWorks, Properties then check ConnectionString property. Click on the “Next” button. Give a name to your new configuration: cfgn_AdventureWorks then click on the “Finish” button.

Browse to your project folder and double-click on the file named “cfgn_AdventureWorks.dtsConfig. Your file should look like this:

Some information’s specific to my computer and SQL Server instance will be different in your file.

Using indirect configuration
Ok, now we have a DIRECT configuration. Our package references the full path of our configuration file. That’s not exactly what we want since once our package will be deployed, it's more than likely that the configuration file will be in the same location (drive, folder, etc.). To circumvent this behavior, we need to make our package to get its connection information (the configuration we just created) in a more generic way: we are going to use an environment variables - we are going to have an INDIRECT configuration.

First of all, we need to create an environment variable. But you need to be aware that our new environment variable won't be visible for our SSIS package or project if BIDS is opened before we create the variable. So, if BIDS is opened, close it.

There are two type of environment variables:

• System: these variables are available to all users that log on the computer.
• User: these variables, as the name of the type implies, are available to the user that create them and log on the computer.

SSIS is able to see both types. It is just easier to use system environment variables since all users can use them. Especially on a server. Several process could use it.

Also, there are several ways to create environment variables. My favorite way is using the "Setx" command line.If your operating system is Windows XP, you will have to download Windows XP SP2 Support Tools  Then, at command prompt, type:

Setx cfgn_AW_Path C:\MyProjectFolder\cfgn_AW_Path.dtsConfig -m

The -m option will make the environment variable "System". Now, open another command prompt window and type :

set cfgn_AW

Your screen should look like this:

Now, start up BIDS again and open your project. Open your package, right-click in the control flow and select "Package Configurations" from the popup menu. Now Edit your configuration that we've created earlier. Select the “Configuration location is stored in an environment variable” and then choose the cfgn_AW_Path environment variable.

Click "Next" and "Finish" and voila, your package reference your dtsx configuration file via an environment variable.

This article showed you how to use indirect package configuration. By using this type of configuration, you can change the location of your configuration files and even rename them. All of this will be transparent to your packages.

 

come  from :http://bi-blogger.typepad.com/etlbi_blogger/2008/05/using-indirect-configuration-with-ssis.html