September 26, 2014

SQL Saturday Charlotte - BI Edition is Next Week!



SQLSaturday Charlotte – BI Edition is only a week away and I would like to share some information about it:

·         We have more than 40 sessions across different technical and professional development tracks.

·         There is content for all skill levels

·         We have a great lineup of regional and national speakers.

·         There are many opportunities to network and connect with other professionals and companies in the area.
 

Space is limited, so if you are planning to attend I urge you to sign up soon and join the 400 professionals that have already registered.
 
I hope to see you next Saturday!

February 19, 2014

Power Query: Consuming SSIS Upgrade Wizard Output File


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.

SNAGHTML2a116fde


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.


image

If you are interested in creating a similar report, you can download a copy of a report template I created: SSISUpgradeResultsReport Template.xlsx


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)

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("D:\SSIS\SSIS Upgrade report all packages 2013 10 09.txt"))}),
    FilteredRowsBlanks = Table.SelectRows(Source, each ([Column1] <> "" and [Column1] <> "#(tab)" and [Column1] <> "#(tab)Messages" and [Column1] <> "The Upgrade is Complete")),
    InsertedCustom = Table.AddColumn(FilteredRowsBlanks, "Custom", each if Text.Range([Column1],0,19)="- Upgrading package" then [Column1] else null),
    FillDown = Table.FillDown( InsertedCustom,"Custom"),
    FilteredRows1 = Table.SelectRows(FillDown, each not Text.Contains([Column1], "- Upgrading package")),
    SplitColumnDelimiter = Table.SplitColumn(FilteredRows1,"Custom",Splitter.SplitTextByEachDelimiter({"("}, null, true),2),
    ReplacedValue = Table.ReplaceValue(SplitColumnDelimiter,")","",Replacer.ReplaceText,{"Custom.2"}),
    ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"- Upgrading package","",Replacer.ReplaceText,{"Custom.1"}),
    SplitColumnDelimiter1 = Table.SplitColumn(ReplacedValue1,"Custom.1",Splitter.SplitTextByEachDelimiter({"\"}, null, true),2),
    RenamedColumns = Table.RenameColumns(SplitColumnDelimiter1,{{"Column1", "Mesage"}, {"Custom.1.1", "Folder"}, {"Custom.1.2", "PackageName"}, {"Custom.2", "UpgradeResult"}}),
    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Folder", "PackageName", "UpgradeResult", "Mesage"}),
    SplitColumnDelimiter2 = Table.SplitColumn(ReorderedColumns,"Mesage",Splitter.SplitTextByEachDelimiter({":"}, null, false),2),
    RenamedColumns1 = Table.RenameColumns(SplitColumnDelimiter2,{{"Mesage.1", "Message Code"}, {"Mesage.2", "Message"}}),
    TrimmedMessageCode = Table.TransformColumns(RenamedColumns1,{{"Message Code", Text.Trim}}),
    Lowercase = Table.TransformColumns(TrimmedMessageCode,{{"Message Code", Text.Trim}, {"PackageName", Text.Lower}})
in
    Lowercase

September 27, 2013

PASS Summit: First Timer Questions

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.
A. Few ways come to mind:
·         Read the abstract of the session: http://www.sqlpass.org/summit/2013/Sessions.aspx
·         Connect with the speaker and ask them directly. You can read speakers Bios and most of them list their blog, website and twitter. This method gets extra points as you network at the same time.
Q. There are so many sessions, which one would you recommend?
A. Ah! a tricky question. I would recommend to start by looking at the schedule and the session abstracts and make sure you consider levels (beginner, intermediate, advanced, etc.) when making your selection. I recommend you use the schedule builder to mark the sessions you want to attend:       http://www.sqlpass.org/summit/2013/UserLogin/tabid/9758/Default.aspx?returnurl=%2fsummit%2f2013%2fSessions%2fBuildSchedule.aspx  and carry the PDF format of the schedule: http://www.sqlpass.org/LinkClick.aspx?fileticket=eNh5qYO0Qkc%3d&tabid=9664  with you at all times. Keep in mind that you can buy the recording of ALL session for $125; which means you can always watch that session that you couldn’t catch. http://www.sqlpass.org/summit/2013/Sessions/SessionRecordings.aspx    

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.?
A. Downtown Charlotte, or Uptown as we call it, is very walkable. There is also a free shuttle service provided by the city that runs during the day: http://charmeck.org/city/charlotte/cats/Bus/ridingcats/Pages/GoldRush.aspx  
     Our friends from SQLSentry are also providing a Shuttle services at night time with stops in interesting places: http://greg.blogs.sqlsentry.net/2013/09/pass-summiteers-guide-to-charlotte-part.html  
     As always, there is an app for that. I use Yelp! To checkout places when I visit a new city. This link shows things around the convention center: http://www.yelp.com/search?find_desc=&find_loc=210+E+Trade+St&ns=1&ls=1a811fb8b865826d#find_loc=210+E+Trade+St,+charlotte+NC   
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?
A. PASS Summit website has a “Connect at PASS Summit” page: http://www.sqlpass.org/summit/2013/Connect.aspx  
     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! 

Q. What about Airport- Convention center transportation?
 A. Taxis:  flat rate from CLT airport to center city $25
      Bus: Route 5, departs from Airport every 20 minutes, and it is a 25 minutes ride to the Charlotte Transportation Center (CTC) which is 2 blocks from convention center. Cost: $2 http://charmeck.org/city/charlotte/cats/Bus/routes/Pages/default.aspx