Swearing a Lot Today

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…

[1] This is a none-too subtle homage to xkcg, for those of you who paid attention to my last post 😉


About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in SSIS. Bookmark the permalink.

2 Responses to Swearing a Lot Today

  1. andrew says:

    Thanks for that clear explanation of your experiences with setx. I am having a similar but different problem. You can use the /F to set variables from a file but again how do you get it to include spaces? Delimiting your value with either single or double quotes appears not to work. If anyone knows the answer to this problem – please comment?

  2. ZotDitzMyo says:

    I just love your pie-chart. Mine today would be all red because of setx madness. Your post did point me in the right direction, namely putting the /M before the argument instead of using quotes!Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s