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: https://github.com/drsqlgithub/SSISDeployTool/tree/1e7f64c0b434939f47f5079d93aaa7a54377d1f2
The file LIBRARY_Variables_DEV looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#The format of this array is: #Index,0 - Name of the variable #Index,1 - The value of the variable #NOTE: It is important to make sure these values are unique at the Index,0 level. # This file is going to be complex to maintain, # so be very careful and follow the rules. $Global:EnvironmentVariableArray = New-Object 'object[,]' 40,2 #Make sure the first index is large enough for the values that you have stored. #Order Doesn't Matter, as long as you don't reuse an index (that would overwrite the value) $EnvironmentVariableArray[0,0] = "SSISServer" $EnvironmentVariableArray[0,1] = "." #------------------------------------------------------------------------------- # Deployment variables $EnvironmentVariableArray[29,0] = "DeploySSISServerLogFileDirectory" $EnvironmentVariableArray[29,1] = "E:\MSSQL\JobLogs\" #------------------------------------------------------------------------------- # General variables $EnvironmentVariableArray[33,0] = "General_AlertEmailAddress" $EnvironmentVariableArray[33,1] = "drsql@hotmail.com" |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
#Paths $Global:ArtifactDirectory = "E:\DeploymentArtifacts\Jobs" #These are databases we work with, along with the server that they reside on $Global:DatabaseNameArray = New-Object 'object[,]' 2,4 #item 1 - the name that we will reference in code #item 2 - the actual name of the database #item 3 - the server where it is located, pulled from global list #item 4 - the database where a snapshot would actually exist, if # this is a snapshot item $DatabaseNameArray[0,0] = "SSISHelper" $DatabaseNameArray[0,1] = "SSISHelper" #Used array to allow decoding of a variable, like this $DatabaseNameArray[0,2] = environmentvariable_decode("SSISServer"); |
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:
|
1 2 |
$DatabaseNameArray[0,0] = "SSISHelper" $DatabaseNameArray[0,1] = $DatabaseNameArray[0,0] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
function global:environmentvariable_decode ($P_itemName){ <# Used to decode an environment variable reference into its actual value. Environment variables are loaded in the Variables_%EnvironmentName% area, allowing for a different version in dev, prod, etc #> $items = $EnvironmentVariableArray.Count; for ($i = 0; $i -lt $items ; $i++) { #if the 0 position value matches (the name), then use the 1 postion as the return value if ($P_itemName -eq $EnvironmentVariableArray[$i, 0]) { $output = $EnvironmentVariableArray[$i, 1] } } if (!$output) { Write-Error "The array token passed in: [$P_itemName] was not found in the environment variable global array" Throw } else { return $output; } }; function global:databasename_decode ($P_databaseName) { <# Used to decode an database name variable reference into its actual value. Environment variables are loaded in the Variables_Global file #> $items = $DatabaseNameArray.Count; for ($i = 0; $i -lt $items; $i++) { if ($P_databaseName -eq $DatabaseNameArray[$i, 0]) { $output = $DatabaseNameArray[$i, 1] } } if (!$output) { Write-Error "The database array token passed in: [$P_databaseName] was not found in the database global array" Throw; } else { return $output; } }; function global:databaseServer_decode ($P_databaseName) { <# Used to decode an database name and return its physical database name. Environment variables are loaded in the Variables_Global file #> $items = $DatabaseNameArray.Count; for ($i = 0; $i -lt $items; $i++) { if ($P_databaseName -eq $DatabaseNameArray[$i, 0]) { $output = $DatabaseNameArray[$i, 2] #Server entry } } if (!$output) { Write-Error "The database array token passed in: [$P_databaseName] was not found in the database global array" Throw; } else { return $output; } }; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function global:databasename_getconnectionStringSMO ($P_databaseName) { <# Used to for a connection string for the databse variable reference. Database variables are located in the Variables_Global file, but their environment based location is loaded into the array during initialization #> $serverName = databaseServer_decode($P_databasename); $physicaldatabaseName = databasename_decode($P_databasename) $connectionString = "Server=" + $serverName + ";Database=" + $physicaldatabaseName + ";Trusted_Connection=True;" return $connectionString; }; |
Now, we can execute the following code, and see the output of the values of the parameters that we have set:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
$Environment = 'DEV' $BaseDirectory = Get-Location #This script should be portable, so I added the path #dynamically #get the function scripts . $BaseDirectory\LibraryFiles\LIBRARY_Functions.ps1 . $BaseDirectory\LibraryFiles\LIBRARY_Variables_$Environment.ps1 . $BaseDirectory\LibraryFiles\LIBRARY_Variables_Global.ps1 Write-Host '----------------------' $SSISServer = environmentvariable_decode('SSISServer'); $AlertEmailAddress = environmentvariable_decode('General_AlertEmailAddress'); $DeploySSISServerLogFileDirectory = environmentvariable_decode('DeploySSISServerLogFileDirectory') Write-Host $SSISServer; Write-Host $AlertEmailAddress; Write-Host $DeploySSISServerLogFileDirectory Write-Host '----------------------' $SSISDB = databasename_decode('SSISHelper') $SSISDBServer = databaseServer_decode('SSISHelper') $SSISDBConnectionString = databasename_getconnectionStringSMO('SSISHelper') write-Host $SSISDB write-Host $SSISDBServer Write-Host $SSISDBConnectionString Write-Host '----------------------' |
The most interesting part of this to me when I started coding powershell was these two lines:
|
1 2 |
$Environment = 'DEV' . $BaseDirectory\LibraryFiles\LIBRARY_Variables_$Environment.ps1 |
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:
|
1 2 3 4 5 6 7 8 9 |
---------------------- . drsql@hotmail.com E:\MSSQL\JobLogs\ ---------------------- SSISHelper . Server=.;Database=SSISHelper;Trusted_Connection=True; ---------------------- |
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.
Load comments