In the Richmond, VA area, I am the organizer for our SQL Saturdays. Among other jobs that this entails, this also means that I am working with SQLSaturday SpeedPASSes.
SpeedPASS History
According to the SQLSaturday FAQ, the SpeedPASS is your admission ticket to a SQL Saturday event. It contains a name badge, admission ticket, lunch ticket (if you paid for it), and a unique raffle ticket for each vendor.
What this means is that you need your SpeedPASS in order to attend the event, and if you choose to participate in the vendor raffles.
My history with SQL Saturday SpeedPASSes
I’ve had the opportunity to participate in many SQL Saturdays, both as an organizer and as a speaker. And I’ve been paying attention to how the events run, and how things can be done to improve upon them.
For the Richmond SQL Saturdays, we have not been successful in getting people to pre-print and bring their SpeedPASSes with them. In the past, we have approximately 40% of the attendees actually bringing their SpeedPASSes. This means that we end up needing to print the SpeedPASSes for the majority of the attendees.
Other SQL Saturday events have tried a multitude of ways to entice people to pre-print and bring their SpeedPASSes. This usually entails a specific raffle item available for just the people that did bring their SpeedPASSes with them.
Dealing with SpeedPASSes at the SQLSaturday event
In order to print these SpeedPASSes, we need to provide:
- Computer
- Printer
- Internet connection
- Admin level access to the SQL Saturday admin portal
What is entailed is that we need to browse to the SQL Saturday admin portal through an internet connection. We then have to go to the registration section, find the person, and download their SpeedPASS. Sometimes we even have to generate the SpeedPASS first. Once downloaded, we can print it out. The final step is for the SpeedPASS sections to be separated – the attendee needs to cut them apart.
What I’ve noticed many SQL Saturday events doing is to purchase a cheap printer for the day, and they raffle it off at the end of the day. This becomes an extra expense for the event. Furthermore, if there is a different raffle item for the enticement to bring their own, this is yet another expense.
What we’ve done in the past
In the past, we have downloaded the SpeedPASSes (which use the Invoice ID guid as the filename), and then used the registrations spreadsheet (which has both the Invoice ID and the persons name) to look up the person, get their Invoice ID, and then find that particular SpeedPASS to print out. Even with making a hyperlink column based on the Invoice ID to be able to open the file by just clicking on the link, this was very time consuming. Of course, this only worked for the folks that had registered prior to downloading the SpeedPASSes – for late registrations, we would still have to generate and download them from the admin portal. This method required at least two people to handle the SpeedPASSes for the attendees that didn’t pre-print theirs.
A couple of years ago (as described in this post) we decided to just pre-print all the SpeedPASSes for all the attendees. This allows us to not have to deal with a long line of people needing to get their SpeedPASS printed out – we only need to print those for attendees that registered after we downloaded and printed them. It still requires a volunteer to handle the late registrations, but they aren’t busy handling SpeedPASSes for a couple of hours.
Another decision we made was to purchase a good quality, high speed color laser printer. This will not be raffled off; instead we will store it and use it next year.
How has this worked out?
Well, the first year, this moved the line from the printer to a cutting station, where everyone used scissors to cut apart their SpeedPASS. We really didn’t have the space for this, so this created some congestion.
The second year, we tried custom printed SpeedPASSes that were printed onto perforated paper. This method had us working until early Saturday morning getting the process working and getting everything printed out.
The printer has reduced our printing costs. We now print almost everything that we need, instead of sending it off to a printing service.
How we did things this year
As things were wrapping up for our event last year, there was an important change made at the SQLSaturday site for dealing with SpeedPASSes. Previously, the admission ticket was sized differently from everything else, which created hassles in cutting and in using perforated paper (and why there were custom-printed SpeedPASSes – so that we could make them fit). However, the change was that all of the labels are now the same size. This means that perforated paper can now be used to print out the SpeedPASS PDF files that are generated at the SQLSaturday site.
Additionally, I was looking for ways to automate some of the manual process of merging the PDF files as described in the previous post, especially with the work necessary in the Excel spreadsheet. The end result is that we now have a new process that we used, and I’m sharing it with everyone to help them out.
The Perforated Paper
We purchased the perforated paper at www.perforatedpaper.com. Select the “Design your own” option, and make the following specifications:
Get the quantity that you need. I recommend creating an account and saving the order. For subsequent orders, you can go to that order and just re-order it – making the process a lot easier.
The PowerShell environment
In order to run the following script, you will need to set up the environment to enable running scripts, and you will need to download and install a module. You will need to run the following from an elevated PowerShell command window (elevated = running as administrator).
|
1 2 3 4 |
Set-ExecutionPolicy RemoteSigned; Set-PSRepository -Name PSGallery -InstallationPolicy Trusted; Install-Module ImportExcel; Update-Help; |
The PDF Merging assemblies
I’m using the PDFSharp v1.3.2 assemblies. They can be downloaded from //pdfsharp.codeplex.com/releases. I used the SourceForge release. Ensure that you get the PDFsharp-MigraDocFoundation-Assemblies-1_32.zip download. Unzip this folder to a location on your computer.
Getting the SpeedPASS data
There are two things that you will need from the SQLSaturday admin portal. The first is to download the spreadsheet of all of your registrations. Under the “Event Settings” menu, select “Manage Registrations”. On that screen, select the “Export to Excel” option at the top-left of the table. Download this file onto your computer.
Next you need to get all of the SpeedPASS files. Under the “Event Settings” menu, select “Manage SpeedPASS”. At the bottom of this page, click the button to “Create a zip file for all the individual SpeedPASS files”. Download this file, and extract the files to a directory.
The new PowerShell script
The new PowerShell script (and usage instructions) is at the end of this post. Use it to generate your merged SpeedPASS file.
Printing your merged SpeedPASS file
Now just open up the PDF file, and print it to your printer. I used the “Fit to printer margins” option. I recommend that you try a page on regular paper, and hold it up to the perforated paper to ensure that things will print fine before printing out the entire stack.
In conclusion…
This process helps us out with the burden of working with SQLSaturday SpeedPASSes. Once printed, they are separated into a few piles for the volunteers performing the check in of the attendees to easily get the pages necessary. The perforated paper avoids the need to have a cutting station for the attendees to cut the tickets apart. This year, the process went very smoothly. If you are a SQLSaturday organizer, I hope that this will help you out.
In my opinion, the only thing that would be better is to get rid of the SpeedPASS system and do something simplified. It’s just too much work as it currently is.
The new PowerShell Script
|
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 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
<# .SYNOPSIS This script will merge the individual SQL Saturday SpeedPASS files for attendees into one PDF file, sorted by name. .DESCRIPTION This script will merge the individual SQL Saturday SpeedPASS files for attendees into one PDF file, sorted by name. It automatically filters for just people attending the event, and then optionally filters based on their lunch status. Those with a comped lunch status are usually speakers and volunteers, and can be printed ahead of time. .PARAMETER ExcelFile The location of the downloaded Registrations Excel file for the SQL Saturday Event. .PARAMETER PdfSharpPath The path containing the binaries for the PdfSharp utility. .PARAMETER SpeedPassPdfPath The path with all of the individual SpeedPass PDF files. .PARAMETER LunchStatus The lunch status of the attendee. This is how we separate speakers / volunteers (with comped lunches) from all other attendees. .NOTES Directions: 1. Download and save PDFSharp Assemblies from //pdfsharp.codeplex.com/releases. Unblock zip (see above link), and extract files to a location. Use this location for the PdfSharpPath parameter. 2. Install the Import-Excel module: Install-Module Import-Excel (requires running Powershell with Administrator rights). 3. Log in to the SQLSaturday admin site & navigate to Event Settings > Manage Registrations. Export to Excel. Save to disk. Use the full path to this file for the ExcelFile parameter. 4. Navigate to Event Settings > Manage SpeedPASS. Generate all SpeedPass (in the week prior to your event, this is done automatically every 4 hours). Download and save to disk. Extract zip file. Use this location for the SpeedPASSPDFPath parameter. If any files won't extract, locate and download those individual files to the extracted location. 5. Run this script, passing in the parameters for: The location of the saved Excel file for the registrations, The location of the PDFSharp assemblies, The location of the Individual SpeedPass PDF Files, Whether you want the registrations with a comped meal (normally speakers and volunteers), All Registrations, or All Registrations except the comped ones. The resulting output file is located in the parent directory of where the PDF files are located. The resulting output file is named "SpeedPassMerge_YYYYMMDD_HHMMSS_.PDF". 6. Print the merged PDF. It is sorted by "Last Name", "First Name". .LINK //www.kendalvandyke.com/2013/09/practical-powershell-merge-sqlsaturday.html .LINK //devblogs.microsoft.com/scripting/simplify-your-powershell-script-with-parameter-validation/ .LINK //pdfsharp.codeplex.com/releases .LINK //www.powershellgallery.com/packages/ImportExcel .EXAMPLE .\CombineSpeedPasses.ps1 -ExcelFile "I:\SQL Saturday\846\SQLSaturday Event Registration.xlsx" -PdfSharpPath 'D:\PDFsharp\GDI+\PdfSharp.dll' -SpeedPassPDFPath 'I:\SQL Saturday\846\SpeedPASS\PDF\' -LunchStatus All This example produces one PDF file consisting of all of the PDF files for the attending registrations. .EXAMPLE .\CombineSpeedPasses.ps1 -ExcelFile "I:\SQL Saturday\846\SQLSaturday Event Registration.xlsx" -PdfSharpPath 'D:\PDFsharp\GDI+\PdfSharp.dll' -SpeedPassPDFPath 'I:\SQL Saturday\846\SpeedPASS\PDF\' -LunchStatus AllExceptComped This example produces one PDF file consisting of all of the PDF files for the attending registrations where their lunch has not been comped. .EXAMPLE .\CombineSpeedPasses.ps1 -ExcelFile "I:\SQL Saturday\846\SQLSaturday Event Registration.xlsx" -PdfSharpPath 'D:\PDFsharp\GDI+\PdfSharp.dll' -SpeedPassPDFPath 'I:\SQL Saturday\846\SpeedPASS\PDF\' -LunchStatus Comped This example produces one PDF file consisting of all of the PDF files for the attending registrations where their lunch has been comped. #> Param( [Parameter(Mandatory=$true)] [String] $ExcelFile, [Parameter(Mandatory=$true)] [String] $PdfSharpPath, [Parameter(Mandatory=$true)] [String] $SpeedPassPDFPath, [Parameter(Mandatory=$true)] [ValidateSet("All","AllExceptComped","Comped")] [String] $LunchStatus ) # Load the PdfSharp Assembly Add-Type -Path $PdfSharpPath; # Define the export path - the parent of the root path. $OutputPath = (Get-Item $SpeedPassPDFPath).Parent.FullName + '\'; $OutputFile = $OutputPath + 'SpeedPassMerge_' + (Get-Date -Format o).ToString().Replace('-','').Replace(':','').Replace('T','_').Substring(0,15) + '_' + $LunchStatus + '.PDF'; if (Test-Path $OutputFile) {Remove-Item $OutputFile}; #Create the filter. Always get just those planning to attend. $Filter = '($_."Registration Status" -eq "Planning to Attend")'; #Add the desired lunch status to the filter. if ($LunchStatus -eq 'All') {} elseif ($LunchStatus -eq 'AllExceptComped') {$Filter += ' -and ($_."Lunch Status" -ne "Comped by Event Team")'} elseif ($LunchStatus -eq 'Comped') {$Filter += ' -and ($_."Lunch Status" -eq "Comped by Event Team")'}; #Pump the filter into a script block $SBFilter = [scriptblock]::Create($Filter); <# Load the excel file. Apply the filter, and sort by "Last Name", "First Name". Get just the InvoiceID column. #> $Invoices = Import-Excel $ExcelFile | Where-Object $SBFilter | Sort-Object "Last Name", "First Name" | SELECT InvoiceID -ExpandProperty InvoiceID; #Create the output object. $output = New-Object PdfSharp.Pdf.PdfDocument; $PdfReader = [PdfSharp.Pdf.IO.PdfReader]; $PdfDocumentOpenMode = [PdfSharp.Pdf.IO.PdfDocumentOpenMode]; #Define counter for the progress meter. $counter = 0; foreach ($Invoice in $Invoices) { $ThisSpeedPass = "$SpeedPassPDFPath$Invoice.PDF" if (Test-Path $ThisSpeedPass) # Check to see if the file exists. { #"Processing $SpeedPassPDFPath$Invoice.PDF" # Load the file and add it to the output object. $input = New-Object PdfSharp.Pdf.PdfDocument; $input = $PdfReader::Open($ThisSpeedPass, $PdfDocumentOpenMode::Import); $input.Pages | %{$output.AddPage($_)} | Out-Null; } else { #Display File not found message "File not found: $ThisSpeedPass" } # Increment the counter, and display the progress meter. $counter += 1; $progressPct = $counter / $Invoices.Count * 100; Write-Progress -Activity "Merge Speedpass Files" -Status "$progressPct% Complete:" -PercentComplete $progressPct; } #Turn off the progress meter. Write-Progress -Activity "Merge Speedpass Files" -Completed; #Notify user that file save is in progress. Write-Host "Saving Destination File: $OutputFile"; $output.Save($OutputFile); #Notify user that we are finished. "Completed"; |
This script needs four parameters to run:
- The full location of the Registrations Excel spreadsheet file.
- The full location of the location of the PDFSharp.dll file.
- The directory where you extracted all of the individual SpeedPASS files.
- Who all you want to merge SpeedPASSes for. There are three options to use, based on the lunch status: All, Comped, and AllExceptComped. The Comped option allows you to separate the speakers / volunteers from everyone else.
When the script is run, it will create an output file in the parent directory of all of the individual SpeedPASS files. It will be named ‘SpeedPassMerge_” plus the date/time in “yyyymmdd_hh:mm:ss” format, plus the lunch status. It will contain the selected registrations SpeedPASSes (sorted by last name, first name).
