Preparing for SQL PASS Summit–Part 2

Since I've attended SQL Pass Summit a couple of times, this year I volunteered to help out with the "Summit First-Timers" program. This program is designed specifically to help people who are attending SQL Pass Summit for the first time to get the most out of their experience at Summit. I've decided to post a series of blog entries here to not only help out my flock of first-timers, but to also ensure that the information is shared with as many other first-time (and alumni) attendees as possible. This is the second posting in the series – Part 1 can be found here. 

Safe(r) Travelling

I’ve heard that as far as cities go, that Seattle (at least the area near the convention center) is safer than many major cities, however as someone who lives out in the country where the most dangerous evening encounters usually involve surprising a skunk or twisting an ankle in a mole’s burrow, any city can be considered a scary place.

• Don’t leave valuables in the hotel room. There’s always the chance that something may “disappear” when the maid cleans the room, or perhaps you may accidentally forget it in the hotel room when heading home and not notice its absence until after it’s too late (those multi-colored bedspreads do an amazing job of making small earrings invisible). It’s better to simply leave those things at home rather than chance loss or theft.
• Don’t tempt the bad guys. In the evenings, you’ll be walking the dark city streets on your way back to your hotel room. You may or may not be able to find a group of others to walk with (my first year, I stayed in a hotel about 10 blocks away and walked back alone each night), so you want to be sure you are not a tempting target for thieves. Expensive, shiny watches or rings are fairly easy for thieves to remove without you noticing. I wear cheap ($10) watch and leave my diamond engagement ring at home.
• Don’t carry a purse. I know many ladies love carrying large purses so they can carry everything that they might possibly need at any time during the day. Leave it at home – if you don’t have a purse, no one can steal it. I carry everything I need in my pockets. Most other items that I carry in my purse are either left at home or are tucked in my backpack along with my notebooks and water bottle.
• Minimize your wallet. Take only what you will actually need – cash, credit card, ID and hotel key. You can leave your store rewards cards, library cards, social security cards, coupons, etc. at home. You won’t need them and they could be lost or stolen, so why bother carrying the extra weight? Besides, without all of those extra items, you probably won’t need to take a wallet.
• Get a money belt. The best piece of “underwear” you’ll ever purchase. It’s basically a wallet you can wear discreetly under your clothing. It is the ideal place to carry your ID, credit card, airline tickets, passport and money. There are several styles available, so research them and pick the one that works best for you. I use one of these instead of my wallet, and it’s thin enough that it is not visible underneath my dresses.
• Pay attention and be alert. Bad guys depend upon surprise for being able to get away with taking your possessions, so when walking the streets, if you are constantly aware of your surroundings and act like you know where you are going and what you are doing, you will more likely notice any potentially threatening people or situations before you get close to them and will easily be able to circumnavigate them. 

 

Luggage

Unless you’re local to the area (in which case you’ll be sleeping at home), you will have to pack some luggage to take all that you will need for the week that SQL PASS Summit will be held.

• Take only carry-on size luggage. The convention is less than one week long, so a small bag should suffice to hold all the clothing you will need for the days you’re away. Besides, if you are flying, you will avoid paying checked baggage fees since most airlines charge for checked baggage. Having lighter luggage will also make it easier for you to walk from the train station to the hotel (saving you Taxi cab fare money), and the lesser burden will prevent you from becoming exhausted lugging your stuff around.
• Leave your laptop at home. It’s unlikely that you will actually need your laptop computer while at SQL PASS Summit. In the break area of the convention center PASS provides some free internet workstations where you can check your email between sessions. Also, most of the hotels have computers you can use for a small fee. If you believe you really would need to have some sort of computer, consider bringing a tablet since it’s smaller and lighter than a typical laptop. For taking notes, I strongly recommend having a college rule lined notebook and a set of pens in various colors.
• Ensure your jacket / raincoat has plenty of pockets. I love pockets – and the more the merrier. It’s amazing how much one can carry in a jacket, especially one full of pockets. I certainly wouldn’t go as far as Eric Le Fou with over stuffing my jacket pockets, but I hope you can understand the potential there. There are many excellent sites that offer travel gear that is designed for holding more than the typical jackets and coats available.
• Bring a small backpack or tote bag. The bag will be handy for holding your notebook, pens, extra water and other items you will need during the day. Also, since you’ll likely collect a bunch of documentation and items from vendors, you will want a place to put them. Please note that in some previous years, PASS provided participants with a backpack or other bag (covered in sponsor logos) at registration. I do not know for certain whether or not they will provide one this year, so be sure to have an extra bag with you.

For additional tips for packing light, visit OneBag.com.

Preparing for SQL Pass Summit

Since I've attended SQL Pass Summit a couple of times, this year I volunteered to help out with the "Summit First-Timers" program. This program is designed specifically to help people who are attending SQL Pass Summit for the first time to get the most out of their experience at Summit. I've decided to post a series of blog entries here to not only help out my flock of first-timers, but to also ensure that the information is shared with as many other first-time (and alumni) attendees as possible.

Planning the trip

• Register! I know, this sounds like a no-brainer, but there are still some who will register at the last minute. Obviously, the earlier one registers, the lower the total cost for the conference. If you haven't registered yet, please send in your registration now.
• Reserve your hotel room. Because of its proximity to the Washington State Convention Center, the Sheraton Seattle Hotel & Towers is the most popular choice, followed quickly by the Westin Seattle. The Official Seattle.gov site has this PDF listing downtown Seattle hotels. There are a lot of hotels within walking distance, so choose your favorite.

Getting there

OK, you've got your Registration in, and your hotel room is reserved. Now what?
Figure out how you want to get here:

• Drive - If you live within 200 miles of Seattle, you may consider driving. Things to keep in mind:
• Cost of Parking - All hotels charge for parking, and so does the Convention Center. Last I heard, the cost was about $22 / day. Don't count on finding on-street parking - and even if you do find a spot, the meters are the least economical way to pay to park.  
• Seattle Traffic - Take the "excitement" of Boston driving, stir in the hills of San Francisco, add the insanity of Mario Kart racing, and sprinkle in the density of LA Traffic and you've got Seattle driving. Personally, when it comes to driving in Seattle, I avoid it at all costs.
• Amtrak - If you live within 500 miles of Seattle, you may consider taking Amtrak
• Very relaxing way to travel.
• The trains offer food, but you may wish to bring your own because the line for the dining car is often very long, and they may or may not have foods you like. The dining car has some wines / beers available for purchase as well.
• The King Street train station in Seattle is a good brisk walk from the Convention Center. It's all uphill from train station to hotels / Convention Center, so be prepared for some steep climbing. Thankfully, this means the walk back is far easier. I believe it takes about 30-45 minutes to cover the distance depending upon your walking speed and whether or not you get lost or stumble upon a really cool shop that distracts you for a while.
• For those who don't feel like walking all the way to the Convention Center, there are buses and taxis. To find the bus from the King Street train station, take the overpass over the train tracks, and walk a few blocks to the east to the International District Chinatown Stration, and catch a downtown bus up the hill.
• Other Trains - Those who are moderately local to Seattle can take metro transit, Seattle Monorail, a bus or light rail. I'm sure you can figure it out if you're a local.
• Fly - If you live far enough away this is likely your best option.
• Fly to Seattle-Tacoma International Airport
• Take Light Link Rail from the airport into downtown Seattle.
• If you rent a car, refer to the section on driving.

Packing

The most import part of packing for any trip is to ensure you're prepared for the local weather. You should check the 10-day forecast online a few days before you leave to have an idea of what to expect. This is Seattle in November, so plan to dress in layers - you'll be inside the Convention Center most of the day, but some rooms will be too warm and others quite cold - and you will also walk outside a little to get to your hotel, have dinner or go out for karaoke. Expect temperatures to range between 40F and 70F, and expect rain - lots of it. Seattle's rain varies from all-out downpour to a light mist that curls your hair and makes everything feel damp. You'll see both. Or maybe sunshine. It depends.

Here are a few key packing tips:

• Footwear - wear shoes that make your feet happy. If you can't walk 5 miles in them without getting blisters, you will be miserable. The Convention Center is huge, and you may have to walk a very long distance to go from one session to the next. I always pack at least two pairs of shoes and alternate between them to ensure that my feet do not have pressure in the same spots every day. Also, thanks to the Seattle rain, you may slog through a puddle that thoroughly soaks your shoes and you will be greatful that you have another pair to change into.
• Outerwear -  I bring a hooded raincoat (Saf-T-Pockets "Portlandia") made from 2.5 layer waterproof breathable fabric. It's light and thin enough to tuck into my backpack when I don't need it, has reflective trim so cars can see me walking at night, and is long enough to keep me dry in the famous Seattle rain. I prefer the hooded raincoat over an umbrella because Seattle weather frequently features high winds which will instantly mangle even the stoutest umbrella. Also, the raincoat doubles as a windbreaker / layer of warmth for when it's not raining.
• Warm layer - Since the raincoat doesn't provide a lot of insulation against real cold, I've found that wearing a hooded sweatshirt as a middle layer provides sufficient warmth for me to handle whatever Seattle throws at me. A suit coat, cardigan, or other light sweater would serve the same purpose if hoodies aren't your thing.
• Regular clothes - This varies depending upon what kinds of fashions you prefer. Pack enough to cover you each day. I've seen
• Jeans and t-shirt (these range from silly messages to corporate logos)
• Khaki pants and polo shirt (often with corporate logo, but not necessarily)
• Business suit
• Shirt & tie with nice pants
• Dresses
• Blouse and skirt
• T-shirt and skirt
• Hawaiian Shirt and shorts

One other item to consider packing : a kilt. Thursday, the day that the Women and Technology (WIT) luncheon is held, many attendees - both male and female - wear kilts. Some of them wear traditional tartans, while others have Sport Kilts or Utili Kilts. Watch for the #SQLKilt hashtag on twitter for additional information and commentary about kilt-wearing at SQL Pass Summit.

SQL Editor “Presentation View”

I’ve often been frustrated by the lack of real estate in the standard SQL query window. Once the query has been run, the results eat up half of the screen, and if you have multiple grid view results from multiple queries in a batch, it can be difficult to examine all of the data without fighting with the UI to let you move the dividing line up.
Here’s what you often see:

I noticed during an online training session with Paul Randal (blog | twitter) that he was using a set up for his SQL query window that to me was more practical – it had the editor in one tab and the messages / other tabs next to the editor rather than below it.
Like this:

It took me a bit of searching and testing, but I finally located the setting and decided to document it here so I wouldn’t forget where it was again.
From the main menu, click on Tools then select Options…. Once the Options dialog pops up, expand Query Results > SQL Server > Results To Grid. In the list of settings, check both “Display results in a separate tab” and “Switch to results tab after the query executes”. Then click [OK].

SQL will save your settings and next query window you open will use the new view.
In addition, in the Options dialog, you may wish to change the font size. That setting is under Environment – Fonts and Colors. I’d only have it large for presentations where I have enough long queries that I need to display a large section of the window. For ones with smaller queries, I’d use “Zoom-it” because I can zoom in on the text I’m talking about and then zoom back out when finished. Zoom-it also lets you annotate the zoomed in window with drawn lines or additional text in various colors. The annotations disappear when you zoom back out, but they’re handy for pointing out key information.
Happy presenting!

One-Minute Headbands

Recently, I realized that I’d broken or lost most of my headbands, so it was time to find a few more. I found some nice ones in a store, but was shocked by the price – $3 each – as soon as I realized that they were made from regular fold-over elastic. I have a nice collection of assorted colors and lengths of fold-over elastic that I’d purchased for various sewing projects. Most of the fold-over elastic I own cost me less than $1 per yard.
I easily put together 5 headbands in about 5 minutes. I think the hardest part was choosing which colors of fold-over elastic I wanted to use for the project.
Here are the basic instructions:

• Cut a 21” piece of fold-over elastic
• Overlap the two ends by about 1/2” (thus making it into a 20” loop)
• Hand stitch the overlapped pieces together
• Try on the headband.

That’s it! Very simple. For those who don’t sew, simply cut the fold-over elastic a bit longer (24-25” is good), then tie either an overhand knot or a square knot to join the two ends. You can adjust the tightness by moving the knot to make the loop slightly smaller or larger.
Here's my muppet Isabella modeling the aqua headband for me.
You can find fold-over elastic at JoAnn's fabric stores, Rose City Textiles (http://www.rctfabrics.com), and Saf-T-Pockets (http://webstore.quiltropolis.net/stores_app/Browse_Item_Details_v2.asp?Store_id=413&page_id=23&Item_ID=1074).

How to Reupholster a Couch

It’s funny that I procrastinated so long about re-upholstering my couch and den chair since the actual time it took to perform the task was only about 12 hours. I have a couch with 6 cushions that were in sad shape – the seams were beginning to split and the seating surface had developed holes where the fabric simply fell apart. I repaired what I could of the original seats, just to keep it looking somewhat acceptable, but eventually I realized that the fabric was so worn out that it simply could not be repaired. The foam cushions had deteriorated to that state in which anyone who sat on the couch felt like the couch was attempting to absorb them into the lower reaches of the seating area.
I decided that we either had to completely replace the couch or at least replace the cushions. I waited until there was a sale on foam sheets (foam is amazingly expensive), then purchased 3 72”x36”x3” foam sheets. I measured the cushions and then marked cutting lines on the foam to match the sizes of the existing cushions. My husband used a kitchen knife to cut the foam into several pieces.
I purchased about 15 yards of denim (using a 40% off coupon at JoAnn’s), then let it age for about 3 years in my stash until I finally decided that the couch looked so bad that it was ebarrassing to have guests see the couch…I then purchased enough zippers by the yard to ensure that I had enough lengths of zipper for the cushions.
Cutting out the pieces was fairly simple: I put the existing cushion on the fabric yardage and wrapped it around the cushion. I then folded over appropriate amount for seam allowance / zipper space and cut it to that size. The width was cut about 1/2” wider on each side of the cushion. I cut the side gussets by measuring the thickness and width of the cushion.
I serged around every piece I cut, then proceeded to assemble them. It was far easier than I thought it would be since the zipper is attached first (attach to one end of the fabric strip, right sides together. Fold over and top stitch the fabric to the edge of the zipper. Then, put the fabric from the other half of the main piece onto the zipper right sides together. Turn right side out and topstitch along the second side of the zipper. On each end of the zipper, bar tack 3 times (once at the edge of the fabric, once about 1/4” in, and again just over 1/2” in…this way the zipper is less likely to open up from the wrong end)
You now have a tube of fabric with a zipper in it. Pin the side gussets to one end of the fabric tube and sew around it. Before you repeat for the second side, be sure to open the zipper at least a few inches so that it can be turned right side out. Then sew second gusset and turn right side out. Stuff with the new foam and then with the old foam (which is so battered that it easily crushes to less than half its original 6” size, thus filling the cushion to the originally intended size).
Place on couch. Done!
Yes, it really is that simple, so don’t procrastinate. 

SSIS - Speeding Up Package Load Time

Your Package Will Load...Someday 

While working with SSIS, I was often frustrated by how long the package takes to load for editing. Frequently, I was simply interested in opening the SSIS packages to see what they did, but really had no need to actually edit them. Sadly, if you try to open an SSIS package on a machine that either does not contain all of the databases referenced in the package or that cannot connect to those databases, you often have to wait an incredible length of time for the package to open before you even have a chance to change the connections to something valid.

I discovered a small trick which cures that dilemma - open the SSIS package in a text editor (the package content is actually XML), make some minor edits, save, then finally open the SSIS package in BIDS.

Delaying Package Validation Until Later

 Open your package in a text editor and search for all instances of

 "DelayValidation">0</DTS:  

Replace those with

"DelayValidation">1</DTS:

This simple change will prevent BIDS from attempting to connect to and validate every object before allowing you to view the content of the SSIS package. It will still attempt to validate an object when you click on that object to edit it, but at least you won't have to wait for it to time out on its attempts to validate every item.

Correct Connection Strings

Open your package in a text editor and search for all instances of

<DTS:Property DTS:Name="ConnectionString">

Check the value for Data Source and Initial Catalog - are these correct for the environment you're working in? Many times, the package may have been created with connection strings specific to the server they were built on and either the server no longer exists or is no longer accessible. By correcting the connection string information prior to opening the package in BIDS, you can speed up the package load time and avoid the flood of validation errors that go along with incorrect connection information.*

=======
* Yes, I know there are some SSIS tricks for handling different connection strings, such as using a configuration file to hold the connection information, however many SSIS packages that people inherit do not have configuration files for the connection information.

Adventures in SSIS: Data Migration

After escaping from technical support to the “greener fields” of Product Engineering, I thought that I would finally be in my element and have completely stress-free workdays.
So untrue.
So painfully untrue.
The new position offered enough stress to make event the most stable person ready to fling primary colored cartoon-like stuffed birds at an equally cartoon-like stuffed pig (who, for some unexplained reason, is lime green rather than pink, and seems to be just a head). Not that I do that frequently, but these poor birds have seen better days, as their ruffled feathers can attest. I suppose I could try to take my stress out on other things, but HR gets a wee bit upset if I throw chairs or coworkers (drat).Fortunately, they don’t seem to mind the occasional Angry Bird™ flying through the cube farm, however, so I fling away.
What is the current stressor?
SSIS.
I must admit that up until now I haven’t touched SSIS at all. I have plenty of experience working with DTS packages, but none with SSIS.
Until now.
There is no easy DTS to SSIS knowledge conversion. They’re completely different animals. They may do similar things, but nowhere near in the same manner. Books Online offers some information, but if you don’t already know SSIS the documentation might as well be in Klingon since it assumes you fully understand everything and are just using the books online for a quick definition check.
Great. And it’s due by the end of February. Which, by the way was yesterday.
Thank GOD it’s a leap year, otherwise I would have had to ensure I learned invisibility instantly.
Some of the sections of the package I was able to copy from other migration packages I found in our Source Control (it’s Tortoise Subversion, BTW), but the rest had to be created by me, and quickly.
Some things I could figure out: get data from Source, compare to Destination using LookUp, then Insert New unmatched data into Destination. Works great unless you have some wiseguy who customizes the data and causes a primary key violation between Source and Destination. Simply inserting a new row without keeping the key value didn’t work because the Destination table does not have identity insert (aka “autoNumber”) on the key. I searched everywhere for ideas—checking  “SSIS Upsert” and equivalents in Bing and Goolgle—to see if anyone had written code that could help me. Most articles assumed that the destination had identity insert turned on.
Thankfully, I had the #SQLHelp hastag on Twitter and received responses from some awesome people* who were able to provide me the hints / guidance that I needed to plow through this disaster waiting to happen. The saving tweet (thanks @EricWisdahl) contained a link to an extremely helpful article by Phil Brammer called, “Generating Surrogate Keys”. It was exactly what I needed. 
Well, mostly….
SSIS is extremely fussy about certain things—C# coding is case sensitive; datatypes must match exactly; math on certain numbers mysteriously changes the datatype. Things like that.
The issue I had was that the Source table use int for its KeyID, and the Destination table used tinyInt. Most searches for an equivalent in SSIs won’t tell you the full story… but I finally found it.
First, several sources tell you that

tinyInt is equivalent to DT_UI1 (single byte un-signed integer),

but if you check the list of datatypes for variables, you’ll note that it’s missing (or if not missing, then named something that is NOT intuitively equal. What does DT_UI1 mean anyway?).
After many false starts (including learning that the C# scripting is painfully case sensitive), I discovered that

the Byte data type works for tinyInt Variables.

The other thing I found was that in SSIS scripts, when you add something to a value, it automatically forces a conversion to int datatype—and it’s very difficult to undo that. What I did was use Eric’s script unchanged, except to the final statement

Row.ClientKey = NextKey;

I added a conversion to byte:

Row.ClientKey = (byte)NextKey;

and it worked.

Now stress is greatly reduced and my Angry Birds and Green Pig are happily noshing on my cereal.



* HUGS! to @onpnt and @EricWisdahl for being “first responders”