Learn Something New Every Day

I recently had to develop an SSIS package that took Excel files from a different server, import them into a SQL Server table, and process the information from them.  Normally, not a hard thing to do.  To complicate matters, the name of the Excel files was always unknown and ever changing, and there was no consistency in what the worksheet was named.  Normally I’d create a generic connection and dynamically change the connection properties in a script task, but the added complexity of having variable worksheet names made that unbearably difficult.

I solved the process by creating a ForEach loop looking for any Excel files in the landing folder.  I elicited the help from a colleague to develop a C# Script Task (I know Java and VB.NET, but C# is a little iffy for me) to convert that file, regardless of what the worksheet is named, to an XML datatable, from which I could process into a landing table in SQL Server via a stored procedure.  In order to accomplish this, given that we’re creating an Oledb connection string to Excel in the C# script, was to make sure the SSIS package was running in 32 bit runtime.

SSISProjectProperties

Running the package manually posed no problem.  Worked like a charm.  I built the package and deployed it and created a SQL Server Agent job to run this package once a day to process any new files that arrived in the landing folder.  The package would fail each time it ran.  Thinking it was a permissions issue on reading the file, I made sure the SQL Server Agent Service account had the necessary permissions to access and read the file.  After verifying this, I ran the package again through the SQL Server Agent and it still failed.  I then looked at the new job I created to see if I had missed anything.  Turns out I did.  For the step that actually executes the package, I missed one important part.  I opened the job, and clicked Edit on the step I needed to look at.  It turns out I missed one little important check box in the Executions Options tab of the job step.

JobStepProperties

The step that executes the package that needs to run in 32-bit runtime also needs to run in 32-bit runtime.  Once I fixed that and saved the changes, the job worked like a charm.

In conclusion, I guess the lesson from this is to make sure your settings are consistent throughout your whole process of design and deployment.  I learned something new today, and I’m glad I did.  This will be very useful in the future.

Posted in General Stuff, SQL Server, SSIS | Leave a comment

Technology (or lack thereof)

DSC_0233Recently Suzi and I were on vacation for a week at a fishing lodge in Ontario, Canada.  I had been there several times before; this was Suzi’s second visit – the first being three years ago.

In the past, this fishing lodge catered to everyone.  They had theme nights for families, hearty breakfasts for everyone, and you felt as though you were a guest in the owners’ home.  There was internet in all the cabins, ping pong and casino night in the rec center, canoes, paddle boats, and bonfires.  Heck, the owners would even visit you in your cabin in the evening, bringing with them a bottle of wine to share.

We packed up our fishing gear, warm clothes, food, and of course a supply of Spotted Cow.  We headed for about an hour north of International Falls, expecting the same as before.  We knew the camp had new owners, but we weren’t prepared for what awaited us.

We knew we wouldn’t have cell coverage up there.  What we didn’t expect, however, was the absence of any internet connection.  When we arrived at the lodge that Sunday afternoon in the pouring rain, we found that we were unable to tell anyone we had arrived safely.  I’ll touch on this again a little later.

Suzi and I were essentially cut off from all communication with the rest of the world.

Was this a good thing?  One could say yes.  We were on vacation, after all.  We were in a different country in the middle of nowhere.  I was getting burned out at work, and I hadn’t had a vacation in over two years.  Just being away felt good.  On the other hand, no one back home knew if we got there safely.  We couldn’t receive any news, good or bad.  Some country could have launched a nuclear attack on another and we’d have no clue about it.

My point in all of this is that we have become so dependent on technology and information.  Especially those of us who work with it every day.  Although we were in what I consider one of the most beautiful places on Earth, it was unnerving to not know what was going on.  No Twitter feed, no Facebook, I couldn’t check work emails (not that I would have anyway).  No SQL Server clusters, no T-Log restores, no VMs.  About the only technology we had was three phase AC current, and the internal combustion engine in our boat motor.

There was a genuine withdrawal – I assume not unlike someone who is quitting smoking (though maybe not as severe of a withdrawal), not being able to get online.  Those of us in technology rely on information, and when we can’t access it, it becomes very unsettling.

Ultimately, we were able to go to the main lodge and access their very limited wi-fi for a bit and let everyone know we were safe and having fun.  We were able to post a couple Facebook updates.  Suzi and I were also able to see that our friends and family were safe after the violent storms ripped through Madison last week.

In conclusion, in this day and age we take technology for granted.  We see it almost as a right.  And when that right is taken away from us, we become lost.  But that might not be a bad thing once in a while.

Posted in General Stuff, SQL Server | Leave a comment

That Time of Year

sqlsat287_web  It’s that time of year again.  What time is that, may you ask?  SQL Saturday season, that’s what time of year it is.  MadPASS, FoxPASS and a  bunch of other volunteers are putting the finishing touches on Wisconsin’s only SQL Saturday, to be held this upcoming Saturday, March 29, 2014, in Madison, WI.  This is our third time hosting a SQL Saturday, and we’ve grown greatly since the first one in 2012.  We are at capacity with 350 registrants, and a waiting list of more than 30.  This is also the first year we have pre-cons!  Jes Borland  and Ted Kruger are hosting a pre-con about tuning your SQL Servers to perform better, David Klee is giving his talk on SQL Server virtualization best practices, and Dan English is hosting one on Self Service BI.

Me?  I’ll be presenting “You’re the DBA.  NOW What?” again.  A 100 level checklist of what to look for when you’re suddenly thrust into the role of Database Administrator.  We’ll cover files, configuration, disaster recovery, indexes, encryption, and a whole lot more.   The first time I ever presented this, or anything, for that matter, to a group larger than a user group meeting was last year’s Madison’s SQL Saturday (you can read about that, if you want, here).  Since then I’ve given it to many other SQL Saturdays.  It remains well received, I think, because I try to keep it simple for those who are just starting out.

I’m looking forward to this one, as I do all of them.  It is great to meet up with people I see far too seldom.  It’s a great day of free learning from the experts.  These speakers spend their own money, time, and talent to help make us all better at what we do.

Quoting Flounder from Animal House  – “This is going to be great!!”

See you this Saturday.

 

 

Posted in SQL Server | Leave a comment

Another SQL Saturday in the books

sqlsat256_web

This past weekend I attended yet another SQL Saturday, this time in Kalamazoo, Michigan.  I took my show on the road, as it were, because once again I was fortunate enough to be selected as one of the speakers.  I wasn’t able to make it to the PASS summit this year, and I was looking forward to seeing some friends and colleagues again, even if it was at a smaller venue.

I forgot about the one hour time difference between Wisconsin and Michigan, and realized on Friday that if I didn’t leave right that second I’d be late for the speakers dinner.  Even with the late start, I felt I had plenty of time to make the trip.  Hitting downtown Chicago at 2:00 pm didn’t seem like it would be an issue.  I didn’t count on the 35 mile construction project (45 mph zone) between Rockford and Elgin, Illinois, nor did I count on a fatal crash on the northbound Dan Ryan Expressway in Chicago, closing that completely, and bringing the southbound Dan Ryan to a standstill for about 45 minutes.  Add to that pouring rain in Indiana and Michigan, and I finally made it to my hotel about 5 minutes before the dinner was supposed to start.

The speakers dinner was awesome.  I got to chat with Joe Fleming for a good long time, and met Stacia Misner for the first time.  I also met up with the usual suspects Josh Fennessy, Allen White, Tim Ford, Frank GillBrian Davis, and Tamera Clark, among others.  Hope Foley wouldn’t join us until Saturday morning.

I loved this event.  It was one of my favorites.  A great crowd, and another full room for my session.  People asked some great questions, and I must be getting better or doing something right, because the reviews from my session were wonderful.  The lunch was a taco bar and it was amazing.  One very pleasant surprise was the appearance of Gareth Swanepoel.  From Jacksonville, Florida by way of South Africa, Gareth is in Green Bay these next couple of weeks on a consulting gig.  He drove six hours to Kalamazoo this weekend just for the heck of it, not to attend anything, but just to say “hi” and help out if any help was needed.

I sat in on a couple sessions.  Brian’s session on automated installs was interesting.  I might have to check that out in more detail to see if we can start standardizing some environments at some of my clients.  Allen White’s session on BIML and PowerShell started out as a complete foreign language to me, but by the end of his session it was all coming together, and now I can’t wait to start playing with this sort of technology.  I can see it playing a role in some of the ETL work I have to do for my main client.  What I got most, though, out of Allen’s session is that everyone has something to offer.  He encouraged everyone to give speaking a try.

The after party was also a lot of fun.  I didn’t sing any karaoke (if I am ever caught doing so, someone please take my car keys away from me), but it was still a good time.  Talked a bit with Aaron Bertrand and got to know him better.  I also talked to Josh a bit, and he gave me a great idea on my next session.  I’ve done a short talk on SSIS before, and now have some ideas on how to expand this one into a full one hour session.

Back at my hotel Saturday night (nice studio suite at the Residence Inn), I set the coffee maker to start at 6:30 because I wanted to get an early start back to Madison.  I didn’t reset the time on  it though.  So, because of the daylight savings time, the coffee actually went off at 5:30.  I tried to sleep through it, but couldn’t.  By then the clock on my phone said 6:15, which was 7:15 the day previous, 5:15 back home in the Central time zone, and who knows what the clock in my truck said.  So, having no real clue what the time really was, I just said to heck with it all and got up, got ready, and headed out.  I ended up pulling into my driveway right around 11:00 in the morning.  Or noon.  I’m still not sure.

Anyway, it was a great event.  Like I said, one of my favorites of all of them that I’ve attended.   Thank  you to Josh Fennessy for organizing it.  And thank you to all of the volunteers – Tim, Tim’s wife Amy, Joe Fleming, and everyone else.  Without the volunteers none of these events could happen.

Posted in SQL Server | Leave a comment

SQL Saturdays

As I sit this evening, sipping a Spotted Cow, watching the Twitter stream of all my friends at the PASS Summit, I wish I was with them. The PASS Summit is always a great time, I’ve been to the last four, but it was not in the cards for me this year.  Even if it was, my workload right now is such that I’d probably be doing more work than attending any of the sessions.

I had submitted a session for the conference in hopes of speaking there for the first time, but it was not selected. The reason? I don’t have enough speaking experience. Fair enough. I’m relatively new to the whole speaking thing, and when I had submitted my abstract I had only spoken at one SQL Saturday and a couple local user group meetings.

With that being the case, I made the decision to submit my session “You’re the DBA, Now What?” to a lot of the local SQL Saturday events in the Midwest.  I’ve been lucky enough to have been selected to a few of them.   Madison, St. Louis, Minneapolis, and Kalamazoo so far.  I think I’ve submitted to a couple others, but I forget, and I haven’t heard from anyone else that I’ve been turned down or selected. 

I was a first time speaker at the Madison SQL Saturday – I blogged about that one before.  This past summer I drove to St. Louis and gave the presentation to another large crowd.  Minneapolis was this past weekend, and I got a lot of questions, which was good.  It helped fill the entire one hour and fifteen minute time slot.  Kalamazoo will be in November, and I am looking forward to seeing some good friends there. 

After this round is over, I will probably shelve this presentation for a while.  I’ve got the next one in mind, I just have to start on it.  I’m thinking the title will be “You’ve been the DBA for awhile, Now What?”  My first presentation was a 100 level course, mostly for beginners and newbies, and this next one will be geared more towards the mid-level DBA.  This should be fun to put together.

 

Posted in SQL Server | Leave a comment

SS Minnow

boat  Very soon I will post something about SQL Server.  I promise.  I’ve got a couple ideas I’m tossing around in my head, and they will be put to paper (electronic paper, I guess) in the near future.  But, for now, another personal story.

Early Sunday afternoon (May 19th) was just beautiful in Madison.  Slight breeze, sunny, low 80s.  Perfect day for a boat ride.  I called up a couple friends and we made the standard arrangements:  Suzi and I bring the beverages, guests bring the food.  Most of the time we just sit out in the middle of the lake, talking, or listening to the radio.  Sometimes we go through the locks at Tenney Park and laze on down the river to Lake Monona, feeding the ducks as we motor on through the no wake zone.  Yesterday Lake Mendota was a little choppy, so we decided to head down to Lake Monona.  When we got to Monona we were surprised that that lake was even rougher than Mendota.  That’s usually not the case.  So, we turned around and headed back up the Yahara River to go through the locks and get back onto Lake Mendota.

Mendota actually calmed down a bit by the time we got there.  We buzzed the south shore, looking at the new Edgewater Hotel construction next to my former workplace National Guardian Life.  Next we headed to the UW Student Union to see all the recent graduates getting their pictures taken.  I then motored full out over to the west side of the lake to view some of the nicer homes and to look at the boat ramp in Marshall Park.  Once we got there we saw that the sky way off to the west was getting a bit darker.  There were a few more clouds overhead as well, and my friend looked at the radar on his phone.  There was a green and red blob to the southwest of us, and the weather channels were predicting it would move south of Madison.  We looked up at the sky and noticed that it was getting a lot darker, and decided to head back to Warner Park (east side of the lake, about a 15 minute journey full throttle) where I had launched the boat.  Just as we were starting back the wind picked up quite a bit, and there were white caps forming on the waves.

It was at this point I stopped the boat and said “Life jackets.  Everyone.  Now.”  No one even tried to argue.  By the time we got the life jackets on the lake had turned black, with 5 foot swells and what I estimate was a 40 – 50 mph wind.  My boat is a little 18 foot runabout, and it was getting tossed quite a bit.  We were taking on an awful lot of water as well, both from the now pouring rain and the waves crashing over the bow and the sides.

After what seemed like an hour – it was probably only 20 minutes – we made it back to the boat launch.  Everyone was drenched and freezing cold, given that the lake temperature was only about 57 degrees.  We had to wait to get the boat out of the water, as there were 4 boats in front of us.  Everyone on the docks pitched in and helped everyone else.  A few people disregarded their own personal safety and jumped in the water to help hold the boats and get them to where they could be tied to the pier until the trailers were in place.   After our boat was safely on the trailer, we went and helped the last boat get tied up.

By the time we got home, 5 minutes later, the sun was shining, the rain had stopped.  I took my friends back to their car in downtown Madison, and as we passed Lake Mendota we noticed that it was as smooth as glass again.

Quite the adventure, not one I want to repeat anytime soon.  We really had no advanced warning of the storm – it just popped up out of nowhere.  But, we’re safe, dry, warm, and ready to go out on the boat again, as long as it’s nice and sunny out.

Posted in General Stuff | Leave a comment

SQL Saturday #206 Wrap-up

Saturday, April 6th, 241 people gathered at the Madison College (formerly Madison Area Technical College) campus for a free day of learning of everything SQL Server.  Many friends whom I hadn’t seen since the PASS summit or longer attended, presented, and volunteered.  And, as usual, I met some new friends.  Oh, and I was a presenter!

My presentation was titled “You’re THE DBA.  Now What?”  I tried to focus on a checklist of things to look for if you’re an accidental DBA thrust into the role of “the database guy”, or if you’re an experienced DBA walking into a new job or environment. It was based on my own experiences of leaving one job for another, and what I found when I walked into Day 1.  I had given this presentation twice before; once at the FoxPASS monthly meeting in Appleton, WI, in early March, and again at the MadPASS meeting at the end of March.  Each time it seemed to be generally well received, and I was expecting perhaps a couple dozen people to show up for the SQL Saturday presentation.  I mean, my time slot was the same as popular speakers such as Ted Krueger, Allen White, and Mike Donnelly and others.  I wasn’t expecting a full room.  After all, it was my very first SQL Saturday presentation.  Who wants to hear from the new guy when you have other talented speakers to listen to?

Ten minutes before my presentation was to begin I followed the advice of Eddie Wuerch and went to the rest room and grabbed a bottle of water.  He wouldn’t give this advice until the very last session of the day, but I must have caught his vibe or something and took care of those two things that could make for a rough presentation on my part.  When I returned I was amazed that people were actually bringing in chairs from the lobby to sit it on my talk.  The room was overflowing!  Nothing flips your stomach upside down more than getting ready to talk to a standing room only crowd.

Because a) it was a large crowd, and b) it was my first time presenting in front of a rather large crowd, I think I could have done better.  There were some things that I slipped up on, and other things that I would have done differently, but all in all people seemed to enjoy it.  The evaluation feedback comments are great, both the positive comments and those that were critical.  I did have a couple people thank me and say that this was exactly what they needed, that they could go back into the office and make their SQL Server environments better.  I guess if I helped at least one person, the talk was a success.  I do need to give a special thank you to Eric Selje, who gave me some great advice later on at the after-party on how to improve my presentation.  Eric realizes that I am new to this sort of thing, and the things he told me I took to heart and I will definitely be able to make my presentation better because of his advice.  After all, I have submitted this session for the annual PASS conference, where if I am lucky enough to be selected as a presenter I will be speaking in front of an even larger crowd.

After my presentation I got to be a room monitor for the rest of the day.  This allowed me to sit in on sessions given by Jes Borland and Ted Krueger, Ed Leighton-Dick, and Eddie Wuerch.  Jes and Ted talked about the joys and the downfalls of consulting, Ed talked about how to get more involved in the SQL community, and Eddie had a great presentation on how to give a technical presentation.  I will definitely be incorporating some of Eddie’s teachings in my future talks.  After all, I was there to learn too.

That evening many of us went to the Ale Asylum for some beer and pizza.  We eventually made our way to The Brass Ring, and around 11:00 we called it an evening.   It is so rewarding to be part of a group that cares so much for each other, and even though we may only see each other a few times a year, we act as though we were together just yesterday, laughing, hugging, and telling stories.  Aside from family, I can’t think of a greater group of people.  But we are sort of our own family, the SQL community, and it’s always great to get together.

I would be remiss if I didn’t give a huge THANK YOU to Gina Meronek.  Her organizational skills and passion were what made SQL Saturday #206 run like clockwork.  Several volunteers helped out, I wish I could have helped more, but collaborative efforts of many people helped make SQL Saturday #206 in Madison a huge success.  My thanks to you all.  I can’t wait for next year.

Posted in SQL Server | 2 Comments