business_intelligence_sql_server_ssas_ssis_ssrs

Simple SSIS package monitoring for SQL Server 2008, Part Two

In part one we developed a query that nets us some cool performance metrics and compacts the big-ugly syssislog into a more readable format.   In part two, we’re going to use SSRS to visualize that data.  If you’re using SQL Server 2012, checkout the Execution And Logging Reports.  For the rest of us pedestrians on SQL Server 2008 or 2005, there is a download button at the bottom of this article filled with SSRS reports that will create an SSIS monitoring dashboard.  These will query the sysssislog and present that data in a similar fashion.   When you execute the report, here is what you should see…

SimpleSSIS1
You’ll have to forgive the metro / modern UI design cues. I’m sure someone out there wants to slap my hand!

I’ll try and explain it from top to bottom.  The red and green dots up top are a heatmap of each packages executions over time.  To the right of the heatmap are tiles that that provide quick info about our environment.  Below the heatmap is a stepped table of executions grouped by computer and then by package name.   Please note the toggle buttons next to the computer grouping.

ToggleButtons

Clicking the details toggle button will reveal the individual run history of the package.

ExecutionHistory1

Once expanded, each row represents a single execution of that package.   These rows have some hyperlinks.  The first hyperlink is in the Messages column.  When clicked, it will display the full unedited list of messages associated with the run.  It does this by calling in a subreport.   The next hyperlink is optional.  It’s inside the Configuration column.  You will only see this link if your package loaded external XML file before firing off.   Clicking it displays that XML file on screen.  It’s a good way to verify which variables were loaded.    Both hyperlinks use the javascript new window trick to keep your report viewers on the same page when drilling down.

="javascript:void(window.open('"+Globals!ReportServerUrl+"/Pages/ReportViewer.aspx?"+Globals!ReportFolder+"%2fRawSysSSISLog&rs:Command=Render&Server="+REPLACE(Parameters!Server.Value,"\","\\")+"&Database="+Parameters!Database.Value+"&executionid="+Fields!executionid.Value.ToString()+"','_blank'))"

Clicking the graph toggle button will reveal the performance graph.

PerformanceGraph1

The black lines are row counts, the red/green lines are status, and the height of the color indicates duration.  Unlike the heatmap up top, the time scale along the bottom of this graph is not linear.  That means whether a day or week passed between runs, they will still be listed side-by-side.


Make sure to deploy the subreport into the same folder as the main report. The connection strings of both reports are dynamic.

="javascript:void(window.open('"+Globals!ReportServerUrl+"/Pages/ReportViewer.aspx?"+Globals!ReportFolder+"%2fRawSysSSISLog&rs:Command=Render&Server="+REPLACE(Parameters!Server.Value,"\","\\")+"&Database="+Parameters!Database.Value+"&executionid="+Fields!executionid.Value.ToString()+"','_blank'))"

Be sure to add your servers and databases to the @Server and @Database parameter lists. If you only have a single log table — or you just don’t need that fancy dynamism — you can delete the parameters and set the datasource connection string for your environment. Enjoy!

Download

4 thoughts on “Simple SSIS package monitoring for SQL Server 2008, Part Two”

  1. A very good set of reports, we have recently started working on a new data warehouse, I have taken your reports and tweaked for our requirements, a very solid base to work upon.
    A couple of things I have changed:-
    – Added distinct SUM of error packages (removed child OnInformation posts)
    – Added distinct SUM of packages executed (same as above)
    – Removed stalled packages from errors and created a new section of ‘Long Running’ jobs.
    – Altered definition of ‘Success’ status as some error packages were creeping in.
    – Extended duration to define stalled/Long Running status as executing jobs were shown as ‘stalled’ depending on report execution time vs job start time.
    – Removed charting information from report.

    I would be interested to know if you have developed any reports over the ReportServer DB?
    These reports have been a great help!

    Regards,
    Andy

    1. Thanks for the feedback! Glad I could provide a solid base to work from.

      I haven’t developed any reports against the ReportServer db, but I can see how the ReportServer db’s ExecutionLogHistory table could be useful to analyze report performance. I smell a future project.
      If you don’t mind, would you care to share the code improvements you’ve made to the status field? They sound useful.

  2. Magic stuff, thank you.
    I had to add another clause to subselect H that counts the PackageRows;
    AND Status = ”Success”

Leave a Reply

Your email address will not be published. Required fields are marked *