Implementing Environmental Variable Values in PowerShell

As a relative neophyte to PowerShell (something I will remind readers of every time I start a blog in this series!), I am not always sure I have the best way of doing things. In this blog, I am going to lay a bit of foundation for my job creation system, by building a set of functions I will use to variablize things like servers, database names, etc. I had often seen this done using Json files in presentations and blogs (and as I noted in the first entry in my using PowerShell to Build SQL Server Agent Jobs Series, I will define my jobs, dependencies, and schedules using Json files.)

This works generally for code and values that are static across environments, but I wanted to be able to default values using calculated values in a fairly obvious (to me) way, so I devised a method using arrays that made it very easy to default one value to another. To my project, I am going to add two new files. One called LIBRARY_Variables_Global, and one LIBRARY_Variables_DEV. The DEV file will also have a counterpart for PROD, QA, etc.

Note: If you read this and think: what a dumb way to do this, please let me know. I will gladly change out this part of the code to make it better.

Files for the project are available on github here:

The file LIBRARY_Variables_DEV looks like this:

The production version of this will have the same names, but different values. Server names to reference, for example. Server principals, etc.

The LIBRARY_Variables_Global file has this in it:

Values like #Paths may change from global to environmental, based on the need. If you have servers that all match, then it is global, if you are testing on a single disk box, and then move to a multi-disk box, that would be different so you would then define $Global:ArtifactDirectory in both the DEV and the PROD variable files.

The $DatabaseNameArray has up to four parts. 1. The name you use to reference it. For example, in my code I will call my SSIS database SSISHelper. Our real database is called SSISManager. If I changed $DatabaseNameArray[0,1] to be SSISManager, all references would change. The $DatabaseNameArray[0,2] entry is to the server. Servers are given a name, such as the SSISServer is our server for moving data around. This is where the SSISDB is located as well. Any database where you need to know its location will go in this array. This decoding trick is why I used an array…If it is easier to do another way, let me know.

In some other locations, I used things like:

This would default the second value to the first. If you want to override the value, just change it from the array reference. To make use these arrays, there are four functions, the first three are super straightforward. Each takes in a token value, loops the particular array, if the value is found, output the value, if not, stop processing.

The next item gives us the thing we often need in accessing the database, a connection string. It uses the other database functions, and formats the name as the SMO or SQL Server commands will need them.

Now, we can execute the following code, and see the output of the values of the parameters that we have set:

The most interesting part of this to me when I started coding powershell was these two lines:

Yes, I can change $Environment to 'PROD' and the file that will be loaded is LIBRARY_Variables_PROD.ps1. This was a game changer for my process, because it made the process rather straightforward to have one set of values per environment.

The output of this code is:

You can see the Server Name that the SSISHelper database is on is the local one (denoted by .). this value is used as a variable, but can also be accessed by the database server name, which then uses the environment variable.

Ok, so this is the last of the “boring” setup entries in this series. In the next entry, I will start creating the jobs by at least adding Job Categories, then all of the other bits and pieces that are needed.