The Datachix Blog











{March 1, 2011}   Finally, a PowerShell script I can get excited about!
Aaron Nelson (Blog | Twitter) and I have a little game we like to play where we drink beer and talk smack about each other’s favorite platform. I’m (mostly) an SSIS developer.  He’s a (SQL Server) PowerShell guy.  He’s always calling SSIS bloated and inferior, while my position is that PowerShell, while handy for many things, is just not suited for ETL work beyond straight-copy-data-pushes.

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.


 

Finished product.



Now With PowerShell:

 

Go to Start>Programs>Accessories>Windows PowerShell>Windows PowerShell>(right click)>Run as administrator


PowerShell opens

 

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:

http://blogs.technet.com/b/heyscriptingguy/archive/2010/06/03/hey-scripting-guy-can-i-use-windows-powershell-to-read-a-text-file-and-update-an-environment-variable-on-remote-computers.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2010/05/20/expert-solutions-advanced-event-4-of-the-2010-scripting-games.aspx



Ben says:

Very informative, and love the classic squiggly “ms paint” lines…and as always staying true to your NERD side ;)



Dork side :) Glad you found it helpful!



I like this. I’ve been wanting to play with PowerShell for a little bit, but I’ve never felt like I’ve had a test case for it. This is fantastic cause it’s a nice, small step into the water, and it’s even useful for something you were working on.



Thanks Matt,
I felt the same way. I knew PowerShell was useful, just hadn’t found a compelling case for using it yet until this. I actually tried to get the larger script working so I could learn how to loop through a batch of servers and deploy the environment variables remotely, but just couldn’t get it working. Still too much of a PS newb. But now it’s on my radar.



Leave a Reply

et cetera
%d bloggers like this: