Blog Post

Practical PowerShell: Merge SQLSaturday Speaker SpeedPASSes Into One PDF


To make things easier on speakers prior to a SQLSaturday organizers are encouraged to print & cut their SpeedPASSes ahead of time and distribute them at the speaker appreciation gathering before the main event. The current iteration of the admin tools makes that an arduous task (at best) – eating a couple of hours that could be better spent doing other things to get ready.


Here's how it works today:

  1. Log into admin site & navigate to Event Settings > Manage SpeedPASS  (1 minute )
  2. Filter the list by typing "Comp" in the Lunch Status text box (1 minute)
  3. Right click the link for each SpeedPASS, choose Save target as..., and choose the folder where you want to save the PDF. Repeat for however many comps you have - sometimes over 50! (30 minutes)
  4. Open and print each PDF (30+ minutes)

Having been faced with doing this this last week for SQLSaturday #232 in Orlando I spent as much time as if I had done this manually figuring out how to automate this (as much as possible) and cut the task down to 10 minutes or less for the benefit of other SQLSaturday organizers. Here's what I came up with:

  1. Log into admin site & navigate to Event Settings > Manage SpeedPASS  (1 minute)
  2. Filter the list by typing "Comp" in the Lunch Status text box (1 minute)
  3. Copy and paste the list into Excel (5 minutes)
  4. Copy the column with the SpeedPASS URLs from Excel to a text file and save it to a text file (1 minute)
  5. Run a script to download the PDF files and merge them into a single PDF (1 minute)
  6. Print the merged PDF (2 minutes)

Enter PowerShell

Being a PowerShell guy I figured step #5 can be done in a few lines of code. PowerShell can handle downloading the PDFs natively but not the merge part. Bingle led me to PDFsharp, a .NET library for creating and modifying PDF documents, and since PowerShell can use .NET libraries we're in business!

Note: The PDFsharp assemblies are compiled against the .NET 4.0 framework. You will need to use PowerShell version 3 or higher for this to work!

Before you can run any PowerShell code you need to download PDFSharp from CodePlex. Make sure you get the assemblies and not the source code! Once downloaded, unblock the ZIP file or PowerShell gets cranky because it's considered untrusted since the file came from the internet.

To unblock a file, navigate to it in Windows Explorer, right click, and choose the Properties menu option. On the General tab, click the Unblock button, then click the OK button to close the Properties dialog.

Unblock File

Once the file is unblocked you can extract the contents; it doesn't matter where but keep your explorer window open so you can copy the path to a DLL you'll use in the PowerShell code.

Next, copy and paste the code below into the PowerShell ISE (you're using PowerShell version 3, right?) and modify the following variables:

  • $PdfSharpPath: The path to the GDI+ version of PdfSharp.dll that you extracted in the preceding instructions
  • $SpeedPASSUrlPath: The path to the TXT file containing the SpeedPASS URls

Now execute the code, go grab a cup of your favorite beverage, and when you get back you should have the individual PDFs downloaded and a single merged PDF file ready to print. The individual PDF files are downloaded into a temporary folder and the merged PDF file will have the same path and name as the TXT file but with a PDF extension instead of TXT.

# Change the following path to where you've put the PDFsharp DLL  

$PdfSharpPath = 'C:\Users\Kendal\Downloads\PDFsharp-MigraDocFoundation-Assemblies-1_32\GDI+\PdfSharp.dll'

# Change the following path to where the TXT file with SpeedPASS URLs is
$SpeedPASSUrlPath = 'C:\Users\Kendal\Documents\SpeedPASSlist.txt'

# Load the PdfSharp Assembly
Add-Type -Path $PdfSharpPath

# Create a temporary path for downloading the SpeedPASS PDF files
$TmpPath = [System.IO.Path]::GetFileNameWithoutExtension([System.IO.Path]::GetRandomFileName())
$TmpPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), $TmpPath)

if (-not $(Test-Path -Path $TmpPath)) {
New-Item -Path $TmpPath -ItemType 'Directory' | Out-Null

$OutputPath = [System.IO.Path]::ChangeExtension($SpeedPASSUrlPath, 'PDF')
$WebClient = New-Object System.Net.WebClient

Function Merge-PDF {
Param($path, $filename)

# Delete $filename if it exists
if (Test-Path $filename) {
Remove-Item $filename

$output = New-Object PdfSharp.Pdf.PdfDocument
$PdfReader = [PdfSharp.Pdf.IO.PdfReader]
$PdfDocumentOpenMode = [PdfSharp.Pdf.IO.PdfDocumentOpenMode]

foreach($i in (gci $path *.pdf -Recurse)) {
$input = New-Object PdfSharp.Pdf.PdfDocument
$input = $PdfReader::Open($i.fullname, $PdfDocumentOpenMode::Import)
$input.Pages | %{$output.AddPage($_)} | Out-Null


try {
$WebClient = New-Object System.Net.WebClient

Write-Host 'Downloading SpeedPASS PDF files'

# Download the individual SpeedPASS files
Get-Content -Path $SpeedPASSUrlPath | Where-Object {
-not [String]::IsNullOrEmpty($_)
} | ForEach-Object {
$SourceURL = $_

$Filename = Join-Path -Path $TmpPath -ChildPath $(Split-Path -Path $_ -Leaf)

try {
$WebClient.DownloadFile($SourceURL, $Filename)
catch {
Write-Host "Unable to download $SourceURL to $Filename"

# Call the merge function
Merge-PDF -path $TmpPath -filename $OutputPath

Write-Host "Merge Complete!"
Write-Host "SpeedPASS files are located in $TmpPath"
Write-Host "The merged SpeedPASS file is located at $OutputPath"

} catch {
Write-Host "Uh-oh, something went wrong..."
Write-Host 'Review $Error to figure out what happened'
} finally {

Remove-Variable -Name WebClient, TmpPath, OutputPath

Admittedly, this is unpolished code - good enough to accomplish what it needs to. If you're PowerShell savvy (or if you're learning PowerShell and want practice in writing & running scripts) feel free to turn this into a script that you can run year after year.



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating