Thursday, February 25, 2010

SSIS Indirect Configurations

One thing that is not clear in BOL and many web articles about Indirect Configurations is how to do it.

  1. Create configuration directly (in a dtsConfig file on the local disk) using Package Configuration Organizer (SSIS > Package Configuration…).
  2. Create environment variable that points to the file (you can use Set command in cmd.exe window)
  3. Restart Visual Studio or you may not see the variable in VS. In some cases you may even need to re-login .
  4. Open the Package.
  5. Reopen Package Configuration Organizer and edit the configuration.
  6. Change it to Configuration location is stored in an environment variable (see Figure) and choose Environment variable from the list. Then press Next, save everything…

ssis indirect configuration.JPG

If only we could change the folder in multiple environment variables in one quick change.

SSIS Convert Indirect Configuration to Direct Configuration

If you switch from Indirect to direct configuration of the package, you will lose dtsConfig file. The proper way to do is to drop Indirect configuration and then Add direct (you will be prompted to reuse existing file).

Setting Environment Variables

A quick way to set environment variables is to add/edit them in registry. They are in following node:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

I don't think that you should use user environment variables.

HKEY_CURRENT_USER\Environment

If you need to change a set of variables, simply create a .reg file with content such as:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]

"dtsConfig_GeoLog"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\GeoLog.dtsConfig"

"dtsConfig_DW-PPC2"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-PPC2.dtsConfig"

"dtsConfig_OLTP-PPC"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\OLTP-PPC.dtsConfig"

"dtsConfig_SMTP"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\SMTP.dtsConfig"

"dtsConfig_MailToFrom"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\MailToFromdtsConfig"

"dtsConfig_DW-Financials"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-Financials.dtsConfig"

You can create this type of file by manually editing nodes in RegEdit and then by Exporting them.

Note: After you create these variables, they may not immediately become available to you. You may need to restart process (Visual Studio) or even re-login.

Simple way to set dtsConfig - SQL Agent step

There is a much simpler way to set location of configuration files then Indirect Configuration - through SQL Agent jobs:

ssis dtsConfig through SQL Agent step.JPG

No comments:

Post a Comment