But today I found a PowerShell feature that I and all of my hard core ETL developer compadres can really get behind: the ability to script the creation of an Environment Variable.
We use environment variables a lot as ETL developers. They are the gateway to seamless deployments. With one environment variable per server configured to hold the string location of an xml configuration file, a developer can release from Dev to QA to UAT to Production without a twinge of right-clicking induced carpal tunnel. I can write more about the magic of Environment Variables for SSIS configurations later, my point is that we use them a lot.
One of my coworkers is working on an extremely large project. He found himself in a situation where he was going to have to set up configurations for over 20 SQL Servers. He asked me if I knew of a faster way to create an environment variable other than the GUI configuration method. I told him I didn’t know for sure, but there was probably a PowerShell script for it. I DM’d Aaron and asked him. He didn’t respond within seconds (WHAT not glued to the Twitter every second,awaiting my tweets?), so I Googled it. The first item on the search took me here to this easy syntax:
[Environment]::SetEnvironmentVariable("TestVariable", "Test value.", "User")
(Note this is not pure PowerShell, but the SetEnvironmentVariable method of the .NET Framework, apparently there isn’t a PowerShell way to create environment variables which persist beyond a process)
I tried it once. It failed with a permissions error. I went back and restarted PowerShell, this time using “Run as Administrator”. Tried the script again and it worked like a charm. I DM’d Aaron again to tell him never mind.
The side by side , play by play:
My task is to create a System Environment variable called “TestVariable”. Its value is to be “c:\configfiles\ETL.dtsConfig”
First, I’ll create the variable with the GUI.
Go to Start>Computer>(RightClickonComputer)>Properties
Click Advanced system settings in the System Control Panel
Click the Environment Variables Button
Click New under User Variables for a variable specific to your windows user and New under System Variables for a variable universal to the entire machine.
Type in the name and value of the variable.
Now With PowerShell:
Go to Start>Programs>Accessories>Windows PowerShell>Windows PowerShell>(right click)>Run as administrator
At the prompt, type: SetEnvironmentVariable(“TestVariable”, “C:\ConfigFiles\ETL.dtsConfig”, “Machine”) –replace work “Machine” with “user” for User level variables.
Finished Product (Same shot as finished product using the GUI)
It’s been a week since I started writing this post and my colleague has informed me that he was able to create the Environment variables on 30 machines using this process and it went much faster than having to slog through the gui 30 times. If I were a guru like Aaron, I’m sure I could even figure out how to run one script on one machine and remotely create as many variables as I wanted, but I’m not that far along yet. Here are some links for folks who have manged to do this successfully: