Tag Archives: SSIS 2012

I Didn’t Hyperventilate!

I gave what is officially my second presentation this week. I presented at the Triad SQL BI (Twitter | PASS) user group meeting and I didn’t hyperventilate! That’s a huge deal for someone like me, who is petrified of public speaking.

It started out a little rough though.

Timeline
Friday, 8/22 (just 4 days before presentation date) – I met Katherine Fraser (SQLSassy) for lunch and she mentioned that their scheduled speaker had just cancelled on them the day before. I asked her what she was going to do and she said unless I wanted to present for her, she had no idea. I jokingly said, “Yeah, sure, I’ll present”. Do not EVER, tell a chapter lead you will present, even if joking around because they will pounce on you! Lesson learned there. I agreed to present a very introductory session on SSIS. I then went home and started to panic.

Saturday, 8/23 – I woke up with a horrible sinus headache and thought I was in the beginning of nasty sinus infection. Now I really started to panic. I sent Martin to the drugstore to buy every sinus medication they had on the shelf. There was no way I could be sick, I could not cancel on Katherine after I had just agreed to present the day before. I proceeded to pound down some Emergen-C and drink about a gallon of water an hour for the rest of the day.

Sunday, 8/24 – I woke up at 4:30 a.m. to take part in the upgrade of major system at work. I felt about the same as Saturday. I pounded some more Emergen-C and worked until 11:30am. After we got the green light from the testers at 3:30 p.m., I went to bed and collapsed.

Monday, 8/25 – Woke up feeling much better, but not great. Pounded more Emergen-C. Started to work on my presentation. Did I mention that I didn’t have anything prepped for a presentation? I’m not a speaker, why on earth would I have a presentation ready to go? Got a call from my boss that the system upgrade wasn’t going so smoothly and had to start firefighting in production.

Tuesday, 8/26 – Presentation day. Got the word from my boss that the system upgrade was still up in the air, but none of the pieces that were broken were anything I could help with or fix. I started to work on my presentation. Just before lunch time I was told I had two conference calls I needed to participate in. Great, another two hours I don’t get to work on my presentation! Finally done with conference calls, when I got a call from my boss, we are rolling back the upgrade and I need to bring the old server back online. Luckily I had been able to create the content of the presentation and test it. I just didn’t have any time to do a practice run through. That was going to have to be enough, it was time to go to Winston-Salem.

I arrived in plenty of time, but I forgot: the power supply for my laptop, my external mouse, speaker evaluation forms and my list of things I needed to take with me to the meeting. Luckily my laptop was fully charged and didn’t die during the presentation (in fact I could have gone on for another 2 ½ hours, thankfully no one wanted to stay that long!). A mouse was provided by our wonderful host, Matt Clepper of Inmar, but not before I had a slight mishap using that @!#$%^& embedded mouse on my laptop. Katherine was well prepared and brought speaker evaluation forms. As for my list of things I needed to bring with me, well, I just had to “adapt and overcome”.

The presentation went pretty well, I didn’t hyperventilate. Sometimes you have to have a very simple goal, just survive without a trip to the ER.

Wrap up
Overall it was a good experience. I think I did a good job of presenting and the feedback I got reinforced that. There were some great ideas on what I could have done better and some great comments on what I did well.

Will I speak again? Probably. I’m not sure I’m ready for a SQL Saturday quite yet, but maybe another couple of times at UG meetings and I’ll think about it. A huge “Thank you” goes out to Katherine for taking a chance and believing in me.

Of course I didn’t sleep at all Tuesday night. I kept thinking, “I forgot to tell them…”

Custom Reports for SSMS 2012

I presented on this topic at the TriadSQL User’s Group meeting on 3/26/13.  I promised I would post my code/solutions, so here it is.  For those that were not able to attend the meeting, here is a brief description of what I am posting.

I am lucky enough to be using SQL Server 2012 in a production environment.  Not only do I get to use the database engine, but I also get to use Integration Services (SSIS),  Analysis Services (SSAS) and Reporting Services (SSRS).

This post is mostly about SSIS 2012 and the aches and pains it relieves compared to previous versions.

One of the great things about SSIS 2012, is the new project deployment model.  Gone is the day when you had to search file servers high and low to figure out where all your SSIS packages lived (if you used file deployment, which I did).   Now all your packages are located in one place.

Another great thing about SSIS 2012 is that it has logging built in and the entries all go to one location, so gone is the day when a rogue developer would send his logging to some obscure table in some obscure database on some obscure server never to be seen by anybody ever again.

Where is this magical place you ask?  The Integration Services Catalog (ISC), that’s where.  The ISC stores your packages along with the metrics for running those packages and sits atop a nice little database called SSISDB.

The ICS also comes with some canned reports for looking at those metrics.  The stepping off point is a report called All Executions.  It can be viewed by right clicking on your project then selecting Reports | Standard Reports | All Executions.  This report is very cool, it provides basic execution information, e.g., how many times it has succeeded, failed, etc.  It also provides start times, end times and duration for each package in the project.  As I mentioned, it is the stepping off point, it has a section with links to three other reports, Overview, All Messages & Execution Performance.

The Overview report is nice; it shows all the parameter values that were used when the package was executed and some detailed execution information.  Definitely comes in handy when troubleshooting.

The All Messages report, is just as it sounds, it shows all the messages that were logged during the execution of the package.  Again, good information for troubleshooting.

My favorite is the Execution Performance report.  Prior to my first glimpse of this report, I had visions of graphs and KPIs that would rival dancing sugar-plums.  Needless to say, I was a little disappointed.  Don’t get me wrong, it has a graph on it, but only the last 10 executions are included.  There is also a listing of the last 10 execution durations.  The thing that gets me is that right above that listing is a three month average and standard deviation.  Really, they couldn’t have included those months of data in the graph?  But I digress.  So I said to myself, “I need more data.”  That’s when the light bulb went off and I decided to get more data all on my own.

The SSISDB is easily accessible and easy enough of follow, especially when using the catalog schema and its views.  I created some SSRS reports in SQL Server Data Tools and was happy as a clam, until I discovered that the SSMS custom reports don’t support images, drill-down or basically any other kind of interactive-ness.  That’s okay, I published the cool reports for the managers to see via the SSRS site and I created a stripped down copy of the reports that I can use within SSMS.

I end up maintaining two code bases, which is kind of a headache, but I work in a small shop where I am the only SQL database related employee, so it’s not that big of a headache.  If you work in a larger shop, you may want to figure out some cool PowerShell script that will sync them up.

I hope you find these reports useful and possibly a jumping off point for you to start your own collection of custom reports.  I am only providing some of the very basic reports that I created, I wanted to leave some of the creativity to you  😉