UPDATE: It turns out that in actual fact the workaround isn't nearly as severe as logging off. All you have to do is start a new command window. Thinking about it this kinda makes sense - I'm guessing that the command interpreter (cmd.exe) collects (for want of a better word) all the environment variables when it starts up.
Thans to Stu Preston for pointing this out to me!
-Jamie
Any developer knows that building solutions in a lab environment is all well and good but its not till you get out into the real world that the foibles and idiosyncrasies of what you're doing really start to scupper your well designed and thoroughly architected solution. Well tonight I faced just such a situation. I'll tell you about it in a bit but first, here's a little demo I'd like you to try. You can do this on Win XP.
1. Bring up a command window
2. Right click on the "My Computer" icon and select "Properties..."
3. Select "Advanced" tab and click "Environment Variables"
4. Create yourself a new system variable called "test". Give it the value "test1"
5. Type "set" in the command window to bring up your list of environment variables.
Can you see your new "test" variable? No, you can't. It aint there!
6. So carry on. Log off your machine and log back on again
7. Bring up a command window and type "set" again. Suddenly "test" has appeared in the list. Weird right? Why do you have to log off to be able to see all your environment variables?
But wait there's more.
8. Go back to "My Computer" Properties-->Advanced-->Environment Variables and change the value of "test".
9. Back to your command window again. What does "set" say now? Its still got the old value in the "test" variable hasn't it?
Don't you think that's weird? I'm using the same commputer aren't I? I'm logged on as the same person? Why is my command interface telling me something different from my GUI?
OK, so what's my point? Why am I telling you this? Well those of you that have used configurations in SSIS will know that you have the option to store the location of a configuration file in an environment variable. This is called an indirect configuration.
I'm using indirect configurations in my current project and the problems highlighted above have been haunting us today. We were running a package in BIDS as normal and it was executing as expected. We could run it without debugging (CTRL-F5) - again, everything was as expected. BUT, try running it using dtexec or dtexecui after changing your environment variable via the GUI and suddenly everything isn't so rosy.
Our environment variable was originally pointing to a legacy confguration file that had some out of date connection strings in it and even referenced some properties that weren't in our package anymore. We couldn't understand why the package executed just fine in BIDS but failed miserably with innexplicable errors when run from the command line. Our errors on the command line talked about things that weren't even in our configuration file - or so we thought. As you've no doubt worked out by now our package was trying to use the legacy configuration file when run using dtexec.
Finding this was horrible - just horrible. It isn't nice seeing something fail and what you're seeing with your own eyes is completely inexplicable - thats what we faced tonight. Happily we found the problem and everything is working OK again but I hope this post serves to stop people suffering the same problems.
I'd love to know why cmd.exe and the GUI can show different results for the same thing. If anyone can answer that please contact me at the usual place!
-Jamie