Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time values instead of using the settings from the config file.

I'm not quite sure I understand HOW to get the external config file to be used at all. I've read articles that say that only design-time configurations that are set will overwrite the load of the external file. Does this mean I can change the variables to blank strings and then they will get overwritten? I can't delete the variable completely! What about integers?

I've seen articles that mention turning OFF using package configurations in the package.

I can use the SSIS Package Editor or an XML editor to change the configuration file path in the package, and then it will use that file's settings "last" (regardless of the external /ConfigFile option), but I don't want to be changing the package. I want one package with Test.dtsConfig and Production.dtsConfig and be able to swap back and forth without changing the package.

What is the recommended way to do this now?

share|improve this question
    
You might find your relief here‌​, on SQLServerCentral forum. Some explanation of the behavior change is here - section Behavior Changes Related to Package Configurations. –  Marian Feb 28 '13 at 8:34
    
Please see the following files, to have an idea about what packages and configs I'm talking about: Config and batches, Test package and Readme. –  Marian Feb 28 '13 at 16:30

1 Answer 1

up vote 5 down vote accepted

You must take into account that when running by BIDS the package will take first the variable value from the config file, and only if the config file doesn't exist, it will throw a warning and the value will be taken from the package.

Now, the situation in command line is a bit different. You can have the following situations:

  1. run the package in cmd line without any config file chosen:

    dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx"
    
    • if the original config file (let's name it Prod) doesn't exist in the same path defined in the metadata of the package, values from inside the package are used and you'll just receive a warning that config file is missing;
    • if the original config file exists and is valid, then values from the config file will be used (inner values will be bypassed);
  2. run the package in cmd line without any config file chosen, but with variable set in the call:

    dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /SET \Package.Variables[checkMe];"outside the package in cmd line"
    
    • if the original config file doesn't exist, then the value is taken from the /SET package call;
    • if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);
  3. run the package in cmd line with a new config file (let's say DEV instead Prod):

    dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig"
    
    • if the new config (Dev) file exists, and old (Prod) doesn't, then values from it are used;
    • if both the Dev and Prod config file exist, then only values from Prod is used (DEV is bypassed even if specified in command line call);
  4. run the package in cmd line with a new config file and a SET statement in the call:

    dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig" /SET \Package.Variables[checkMe];"outside the package in cmd line - DEV config"
    
    • if both config files exist, Prod will be used, all others ignored, even the SET;
    • if no config file exists, SET value will be used;

So, in short, if you want to use a new config file you'll have to rename/move the old one and call the package with /configFile. If that's not enough and want to override even new config file, then use the /SET variable. Or you can bypass any config file and just use /SET statements in the batch call.

Hopefully that will shed some light in your possibilities.

share|improve this answer
    
So it sounds like one of the big problems is that my path on development box is identical to taht on the server during execution so that the path to the config in the package is always valid. –  Cade Roux Feb 28 '13 at 19:57
    
So I think I should have Dev, Test and Prod, have the package always point to Dev and never have a Dev config on my Server and then I can make multiple configs on the server and be able to run against different configs at will since the Dev config in the package will never be found. So I can debug using the Dev config, but then I will have trouble running it from the command line on the dev box because it will find that dev config. –  Cade Roux Feb 28 '13 at 20:00
    
I assume you agree that this is really a lot more complex than it should be, right? Like they got new one little feature (the reloading) in the change to 2008 but killed the most common use scenario of the package configurations. –  Cade Roux Feb 28 '13 at 20:03
    
Well, YES, I agree that's way uglier than it should be. It took me a lot to figure this out, because I've always forgotten to rename the original config file :-). I'd have liked it more to be in the following order: SET, /configFile, original config, inner package value. It would've made more sense for me.. –  Marian Feb 28 '13 at 20:04
    
None of the articles I read really mention that it's the original config file being accessible which is a lot of the problem. Now I see why they published that SSIS Package Editor so you could change that within the package without having to open BIDS. –  Cade Roux Feb 28 '13 at 20:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.