SQLPam's Blog

January 26, 2011

The Speed Pass process at SQL Saturday #62

Filed under: SQL Saturday — sqlpam @ 8:08 pm

Recently, I put on SQL Saturday #62 with the assistance of Jose Chinchilla and many other volunteers. Since the biggest black eye from last year was the registration process, I have spent the past year trying to think of ways to make the process easier. I came up with a process that Jose dubbed “Speed Pass”. Since the big slow down with registration is finding the registrant’s personalized raffle tickets and name badge, I decided it would be easier if the registrant was able to print their own materials and bring them to the event.
Since the process is not currently part of the SQL Saturday site, more work was required than should be needed once the process is available on the site. Here is what I had to do to make it happen outside of the site.
The first step was to bring the registrants into a local database where I could access them. This meant printing a report available to the SQL Saturday admins and downloading it into Excel format. I then had to strip the title off the report so the first line had the column headings. I then stripped the spaces out of the headings to make cleaner field names. I used the Tasks/Import process to bring the data into an already created table which defined the fields more accurately. I then used a script to append any new registrations into a “permanent” registration table set up with primary keys and additional info I was extrapolating from existing data or updating as new data was collected.
The second step was to create the SSRS reports to print the personalized info for each registrant individually. Each report would accept the registrant’s primary key as a parameter and generate the report. The tough part was getting the information to line up properly when output to PDF. Once this was achieved I set up a File Share subscription for each report and modified it a little in the ReportServer database to allow for On Demand subscriptions. I went this route so that future users were not required to utilize Enterprise edition.
Basically, an On Demand subscription can be triggered via a script anytime a user needs it. This is based on a blog published in 2007 by Jason Selberg. A stored proc will manipulate an existing Subscription record so that the time to trigger it is now. Once the report is finished, the stored proc restores the Subscription record back to its original form.
The third step is to generate the file shares. This requires a script that loops through the registrants and generates 2 file shares, one for each report. These files were named based on the registrant’s primary key. They were stored to an isolated folder on my machine. This process took almost an hour per 100 registrants. Most of that processing time was spent generating the PDFs.
The next step was to actually email the PDFs that were generated. This was handled via a script that created a personalized email with the 2 PDFs attached. The emails were sent via dbmail. My biggest hurdle on this was that my ISP blocks port 25 and my only mail servers only used port 25. I ended up using my air card which was on a different ISP. One lesson I learned was to include the primary key in the subject line to make tracking those using Speed Pass easier.
The final piece of this was to track the registrants that opted to use Speed Pass. They let me know via an RSVP email. These registrants were marked off my list to print for the day of the event. A second script generated PDFs for those not using Speed Pass and these were taken to the printer the night before the event.
I had 450 registrations the night before the event. 150 people had responded that they were printing their own reports. On the day of the event, I received 175 speed passes printed by the registrants. I was able to tell the source because the emailed speed passes were a different size than what were printed for day of event. We had around 320 people attending, 305 were actually registered and went thru registration. 15 registered the day of the event.
The biggest thing for me was that there was not a bottleneck in the registration line this year – a big improvement over the previous year. The event evaluations applauded Speed Pass as allowing them more time with the sponsors. The only downside I saw to the process is that people overprinted the materials to allow them to stuff the raffle boxes. I had some names that I found duplicated 5-10 time in each raffle box. That will be my issue to resolve for next year.
I will blog the On Demand subscriptions in detail in the near future. Any event organizer willing to try the process is welcome to contact me. I will be very happy to share the code.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: