Recently Jess Pomfret (b|t) wrote a post on executing a folder of SQL scripts against a SQL Server instance. It's a great post I recommend checking out, and it reminded me of something I have been meaning to blog about for a while: creating custom SendTo options in Windows that execute PowerShell scripts.

SendTo

If you are not familiar, SendTo options are those available when you right click on a file/folder in file explorer and select the Send To option in the menu. When you use this option, the currently selected files/folders are passed to the SendTo shortcut as a space delimited list of files and folders. This is important to know so you better understand what needs to be done to read that list.

The PowerShell

The PowerShell code is pretty straight forward, the script will ask the user for a parameter value (this could be used with Jess's example to enter a server name), and then simply list the files and pause for the user to press Enter. This script could be customized to do almost anything you need:

param(
    [Parameter(ValueFromRemainingArguments=$true)]
    $FilePath
)

while ( -not $MandatoryParameter) {
    $MandatoryParameter = Read-Host "Enter this Mandatory Parameter"
}

# Expand folders into scripts
Get-ChildItem -Recurse -Path $FilePath | ForEach-Object {
    Write-Host " - $($_.FullName)"
}

Pause

There are a few important things to know about using scripts in this way. First, you see ValueFromRemainingArguments above? That is how you handle the space delimited list of files passed in when you use "Send To". Second, if you are used to the typical behavior of adding Mandatory parameters that prompt the user for input, that doesn't work here. In this case I like to just use a while loop that loops until the variable is set. If this were a production script I would also do some validation in that loop to make sure the user supplied value makes sense.

Creating the Shortcut

While not required, I find it easiest to put the above code in a script file directly in the SendTo directory. You can get to that directory by either opening file explorer and typing SendTo in the address bar, or by browsing to %APPDATA%\Microsoft\Windows\SendTo. Just putting the script in that directory isn't enough though, we also have to create a new shortcut. To create the shortcut browse to the SendTo directory, right-click, and select New > Shortcut. This will open the new shortcut wizard.

The first thing it asks for the location of the command. Unfortunately you can't just browse to the path of the script and expect it to work, you also have to add the powershell.exe path to the command. Assuming a script named SendToTest.ps1 exists you would enter the following:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -file %APPDATA%\Microsoft\Windows\SendTo\TestSendTo.ps1

Next it asks for the name, this is what will show up in your Send To menu, so make it good! In this case I will just use SendTo Test.

If you have a set of static parameters you would like to pass to your scripts, you can either set default values right in the script, or supply them as parameters when creating the shortcut. For example, adding -Parameter "MyValue" to the command line above.

Using it

Now for the moment of truth. Select a group of scripts and try out the new shortcut:

If it all worked you should see output similar to this:

Enter this Mandatory Parameter: TestValue
 - C:\Users\mark\test_scripts\4_TestScript.sql
 - C:\Users\mark\test_scripts\5_TestScript.sql
 - C:\Users\mark\test_scripts\6_TestScript.sql
 - C:\Users\mark\test_scripts\7_TestScript.sql
 - C:\Users\mark\test_scripts\8_TestScript.sql
 - C:\Users\mark\test_scripts\9_TestScript.sql
 - C:\Users\mark\test_scripts\10_TestScript.sql
 - C:\Users\mark\test_scripts\1_TestScript.sql
 - C:\Users\mark\test_scripts\2_TestScript.sql
 - C:\Users\mark\test_scripts\3_TestScript.sql
Press Enter to continue...:

If you didn't see this glorious output, and instead a window opened and then quickly closed, you likely have to adjust the execution policy. I personally set mine to bypass, but make sure to read up on this option before changing it. If you are confident in how I have my machines configured, run this to get around this issue: Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope LocalMachine -Force

Thoughts

The coolest thing about this is that it's all just standard PowerShell. If you are using it for deploying a set of scripts you could integrate code to look up a server list via a CMS server, you could introduce runspaces to do things in parallel, you could even tie it into a ticketing system to report when scripts were deployed. The main point thought is that it is a great, low friction, way to start setting up automation for common tasks.