I’ve been thinking today of making up a t-shirt that expresses my opinions on Windows environment variables, but I wouldn’t be able to wear it anywhere. It’s been one of those days, where 6.02×10^23 things have gone wrong. None of them was noteworthy on its own, but when viewed in the aggregate, they made for a very frustrating day.
As you can see, most of my mental anguish today was caused by Windows environment variables. Specifically, I’ve been fighting with the SETX utility, which I use to define environment variables for indirect XML configurations for my SSIS packages. I’ve been using this setup for many months, but have only recently discovered the SETX utility, which is new to Windows Server 2003 and Vista; before now I’ve been using Registry scripts to define the environment variables, an approach which has drawbacks and gotchas of its own.
In any event, I define a package variable in my master packages which stores the root deployment folder – this (when combined with configurations and property expressions) allows me to deploy my packages to arbitrary file system folders as my clients’ environments and corporate policies dictate. It works quite nicely once the environment variables are created. But I set this variable with an environment variable configuration, and based on the logic in my packages (which is now quite well established, and would be a major pain to revise) the path stored in the environment variable needs to end with a trailing backslash, like this:
It works. It’s been working for months, because I have a batch file that contains a line that looks a lot like this:
SETX /M SSIS_Root_Folder C:\DeploymentRootFolder\
That’s right – I have an environment variable that contains my path in the format my packages require, and I can verify it by running the SET command and testing:
Yay! It works as desired and as expected!
But today I deployed my packages to a folder that contained a space in the path. (How this scenario could have remained untested for so long is only my own fault, I know.) I updated the batch file to contain a line that looked a lot like this:
SETX /M SSIS_Root_Folder C:\Deployment Root Folder\
When I ran it, I got back output that looked quite a lot like this:
ERROR: Invalid syntax. Default option is not allowed more than ‘2’ time(s).Type “SETX /?” for usage.
WTF, you ask? WTF indeed! Ahhh… that must be it – spaces in the path. Silly me. I’ll just wrap that path in quotes and all will be good once more. I next updated the batch file to contain a line that looked a lot like this:
SETX /M SSIS_Root_Folder “C:\Deployment Root Folder\”
It worked – hooray! Of course, how could it not work? This is how Windows command line utilities always work, right?
But, of course, when I attempted to run my packages, it did not work. In fact, it did the opposite of work. When I got around to running the SET utility again to check the values actually stored in the environment variable, I found this:
SSIS_Root_Folder=C:\Deployment Root Folder”
WTF? Oh! I see, it’s treating the backslash as an escape character and then treating the ending quote as part of the variable value. Not quite what I’d expect, but I can see the logic. Easy enough to work around, right?
SETX /M SSIS_Root_Folder “C:\Deployment Root Folder\\”
SSIS_Root_Folder=C:\Deployment Root Folder\
But this afternoon either I was doing something subtly different (the more likely explanation, I know) or else this wasn’t actually working. I ended up with two lines in my batch file that looked like this:
SETX /M SSIS_Trailing_Backslash \
SETX /M SSIS_Root_Folder “C:\Deployment Root Folder”%CIA_Trailing_Backslash%
The key, of course, was the backslash as the escape character and the quotes to support spaces in the file names, but it was a long, aggravating process to make it work correctly. Hopefully tomorrow will be a better day…
 This is a none-too subtle homage to xkcg, for those of you who paid attention to my last post 😉