February 19, 2014

Have you ever had the need to create summary and detailed reports to understand the results of a SSIS upgrade exercise such as packages by type of errors and warnings; the number of packages that upgraded successfully/unsuccessfully?

Upgrading SSIS packages is in most cases a straightforward exercise using the SSIS upgrade wizard. However, the output file produced by the wizard with the upgrade results (warning, errors, upgrade status, etc.) is very verbose and formatted in a way that makes it very hard to consume when upgrading a large number of packages.

I recently experienced this pain when working in a project that required to upgrade more than 1,800 SSIS packages that produced an upgrade output file with more than 35,000 lines worth of messages! See snippet of the output file below.

Since I typically use the output file to get a better sense of the effort involved to complete the upgrade of all packages, I quickly realized that for this project I would need to come up with a way to consume such a large output file. Fortunately, I was able to create an excel report like the one below using Power Query in a very simple way. As you can see below, it is very easy to get a sense of how difficult the upgrade is going to be. In the example below, I can quickly see there are only 14 packages that had errors ad 599 with warnings. Additionally, I can easily get a break down of the type of errors and the get the list of packages by message code/type.

Note: Make sure you have Power Query for Excel installed and you edit pqSSISUpgradeReport Power Query query to point to the location where your SSIS upgrade wizard output file is stored ( See query below)

September 27, 2013

Besides speaking at PASS Summit 2013, I am also volunteering as PASS buddy to help a small group of people that is attending the conference for the first time and make sure they have a good Summit experience .I have gotten very good questions from my group and I thought I would share them along with my recommendations - in case they are relevant to you as well.

Q. I see an interesting session and I was wondering how I
could get further information about it beforehand so I am confident it covers
what I want.

Q. I would like to connect and network with some speakers.
What is the best way to approach them?

A. ah!This is another
tricky one as we all have different styles and level of comfort when
approaching people we don’t know. Most speakers will arrive 10-15 minutes before
their sessions and will hang out and take questions at the end. You can
approach them at those times and introduce yourself. You can follow up with a
LinkedIn invite, Twitter, etc. Note: the minutes before the presentation may be
stressful for the speaker, so be mindful of their time.

Q. This is my first time coming to town. What are good
places to hang out/eat, etc.?

Q. Will WI-FI be available at the convention center?A. Yes, but keep in
mind there will be a few thousand people trying to use it as well.

Q.Should I carry my
laptop or tablet?

A. I would recommend to go around as light-weighted as
possible as days are long and you want to save as much energy as you can. I
would carry the lightest device I can live with. Please leave the server at home.

Q. I heard there other activities and after hour events. How
do I know about them?

Events range from
Luncheons, Karaoke parties to 5k walk/runs so there is something for every
taste. If you have a Twitter account, then you may want to follow @SQLPass and
watch #SQLPASS and #Summit13 hash tags. If you don’t have an account, then just
get one!