Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #94 – PoSH Goes Dynamic

TSQL2SDAY-150x150It’s another T-SQL Tuesday, folks! Rob Sewell (b|t) is hosting this month’s challenge. (Thanks for hosting, Rob!) The topic is to talk about PowerShell, or PoSH for those in the know. Check out Rob’s post for details about the challenge and T-SQL Tuesday in general.

I am by no means a PowerShell expert. I first started working with PowerShell a couple of years ago when co-workers introduced it as a way to create “installers” to deploy ETL packages to our clients. My understanding was that it was like the command prompt on steroids. You could execute basic commands like you did in a command prompt, but it had the bonus where you could extend its functionality with your own functions or modules.

I must say that it took me a while to get the hang of PowerShell. It wasn’t until I started to use it to create my own ETL project deployment scripts that I started to understand and appreciate it and figure out how to use it to fit my needs.

For this month’s topic, I’d like to share the most complicated task I’ve been able to do in PowerShell: Create dynamic parameters for my install scripts. The scripts that I created prompt the user for a large number of parameters and I use Powershell to do a variety of tasks – from setting up SSISDB if it hasn’t been created to creating the folders and environments for the ETLs to running various scripts to create database objects and SQL Server Agent jobs. While most of the settings need are the same for everyone, the client’s set up may mean that some of the required settings are different based on what they have installed. Instead of managing multiple scripts to handle these differences, I am using dynamic parameters to ask the user for the right pieces of information based on what they say they have set up.

I’ve created a stripped down version of how I got this to work. Here’s how it works:

  1. The script has an initial parameter. In my example, it’s WorkOrPlay where I’m expecting either Work or Play as the responses.
  2. It then creates a function called ReturnMessage. ReturnMessage uses the parameter declared in the script and then it accepts parameters of its own. Notice that the first parameter set up the same way as the initial parameter in the script. But then you’ll see the DynamicParam section, where the dynamic parameters are set up. Once the dynamic parameters are defined, we then return the updated parameter directory so we will get prompted to supply the values.
  3. The Process section sets up the commands and actions to take, based on the values that are given by the user. Because this is just an example, I’m just print out statements. (I have read that you shouldn’t use Write-Host to print these statements, but it’s what I know. I’m looking forward to reading the other posts to tell me what I should use instead.)
  4. The final line calls the ReturnMessage function. Without this piece, nothing gets done.

Here’s my test script in full. You can also use this pdf version of the script to copy and paste the text into a file and save it as dynamic_param_test.ps1 for testing on your own. (I know there is a better way to post this but I don’t think I’m quite set up for that yet.)

param(
  [Parameter(Position=0, Mandatory=$true, HelpMessage="What message would you like: work or play?")]
  [ValidateNotNullOrEmpty()]
  [System.String]
  $WorkOrPlay=""
  )

function ReturnMessage {
  [CmdletBinding()]
  param(
    [Parameter(Position=1, Mandatory=$true, HelpMessage="Are you sure? Yes or No")]
    [ValidateNotNullOrEmpty()]
    [System.String]
    $AreYouSure=""
    )

  DynamicParam {
    if ($WorkOrPlay -eq "Work")
      {#create a new ParameterAttribute Object for Work
      $NewWorkParam = New-Object System.Management.Automation.ParameterAttribute
      $NewWorkParam.Position = 2
      $NewWorkParam.Mandatory = $true
      $NewWorkParam.HelpMessage = "Is it beer:thirty yet?"

      #create an attributecollection object for the attribute we just created.
      $attributeCollection = new-object System.Collections.ObjectModel.Collection[System.Attribute]

      #add our custom attribute
      $attributeCollection.Add($NewWorkParam)

      #add our parameter specifying the attribute collection
      $WorkParam = New-Object System.Management.Automation.RuntimeDefinedParameter('IsItBeerThirty', [string], $attributeCollection)

      #expose the name of our parameter
      $paramDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
      $paramDictionary.Add("IsItBeerThirty", $WorkParam)
      }

    if ($WorkOrPlay -eq "Play")
      {#create a new ParameterAttribute Object for Play

      $NewPlayParam = New-Object System.Management.Automation.ParameterAttribute
      $NewPlayParam.Position = 2
      $NewPlayParam.Mandatory = $true
      $NewPlayParam.HelpMessage = "Beach or Mountains?"

      #create an attributecollection object for the attribute we just created.
      $attributeCollection = new-object System.Collections.ObjectModel.Collection[System.Attribute]

      #add our custom attribute
      $attributeCollection.Add($NewPlayParam)

      #add our paramater specifying the attribute collection
      $PlayParam = New-Object System.Management.Automation.RuntimeDefinedParameter('BeachOrMountains', [string], $attributeCollection)

      #expose the name of our parameter
      $paramDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
      $paramDictionary.Add("BeachOrMountains", $PlayParam)
      }

    return $paramDictionary
    }

  Process {
    #Get the values from the dynamic parameters
    $Work=$WorkParam.Value
    $Play=$PlayParam.Value

    if ($Work)
      { 
      if ($Work -eq "No")
        {Write-Host "Hang on - just a little while longer..."}
        else
        {Write-Host "woo hoo!"}
      }

    if ($Play)
      {
      if ($Play -eq "Mountains")
        {Write-Host "Grab some granola and water and let's go for a hike."}
        else
        {Write-Host "Grab a swimsuit and towel and let's go!"}
      }
  }
}

ReturnMessage

When I run the dynamic_param_test.ps1 script, the results look something like this:

DynamicPoSHExample - Play
Here’s what happens when I say “Play.”
DynamicPoSHExample - Work
And here’s what happens when I say “Work.”

It took me a while to find an example how to do this so hopefully someone else who is looking to do something similar will be able to use this as an example as well.

I’m sure there is a more elegant way to get this done but this works. And as I get more comfortable with PowerShell, I’m sure I will be able to make those edits as I learn what they are. I’m still learning all the ways people are using PowerShell and I’m looking forward to figuring out how I can incorporate those in the future as well.

Happy PoSH-ing!

 

Advertisements

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