Overridding SSIS Environment variable

问题:

I have set up a Package Configuration where the Configuration Type is (Indirect XML Configuration File). Where the Environment Variable is pointing to the C:\SSIS\MasterConfig\MasterConfig.dtsConfig file.

This working great and allows easy migration from Test to UAT to Production.

The problem is in our Development environment where each developer has their own DB. I am trying to set up an Agent Job for each developer where the Agent Job would override the Master Configuration File. The agent job command line is":

/FILE "C:\SSIS\Packages\Developer1\LoadPackage.dtsx" /CONFIGFILE "C:\SSIS\Packages\Developer1\Developer1_Config.dtsConfig" /X86 /CHECKPOINTING OFF /REPORTING E

Using 2008 R2.

I was expecting that the /CONFIGFILE "C:\SSIS\Packages\Developer1\Developer1_Config.dtsConfig" would be used in stead of the C:\SSIS\MasterConfig\MasterConfig.dtsConfig file.

Only the Master Config file is being used. Any ideas?

回答1:

The problem you are running into is covered under Defining a Configuration Approach for Integration Services Packages Basically, the command line configuration is being applied but then overwritten by the design time value.

I'm not sure what your solution would be though. Semi-similar situation here except we had to deal with multi-instanced boxes (dev and test shared a physical host). To get around that, we skipped the environment variable bit and decided that design-time value would always point to dev. Test, UAT and PROD would only be running packages via SQL Agent so our jobs explicitly define the connection string to the configuration resource. Yours is a backwards scenario though, design-time values is fine everywhere but dev.

回答2:

Here's what we do.

All variables are named the same whether it points to Production, QA or Dev (or even to local). What we change are the variable values pointing to the config files.

We create config files that have all of the appropriate connection information for each box. So we'll have at least 3 separate config files for each database. We name them DB_Prod.config, DB_QA.config, DB_Dev.config, and then DB_Joe_local.config (if you want to have one that points to your local db.

We then create .bat files that sets our variables to the right environment. I have 3 different ones, one for QA, one for dev, and one for my local environment.

the environment variables are ll named things like DB1_adonet, DB1_ole, AS400, etc. With no indication of QA, prod, etc.

It's a slight pain in the ass to set up, but once you start using it, the only issue is remembering what enviroment you've set yourself to. Also, you need to remember that you need to open and close dev studio between environment changes as the values are cached. Also, if you happen to run a package localy, it will use your environment variable values, and not the box you are running it from.

Final note, we have all of the config files checked into TFS. Makes for easy distribution of files.