THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SSIS ReportingPack v1.1.1.0 – Execution Stats visualised in a Gannt chart via usp_ssiscatalog

There are many SSRS reports in SSIS Reporting Pack and the one I’ve perhaps spent most time on is the executable_duration report which provides this visualisation:

image

I happen to think this is really useful because it shows the start time and duration of each executable relative to all the other executables in an execution. It does it by leveraging the execution_path property of an executable (which I am on record as saying is, in my opinion, the best feature in SSIS 2012).

I have wanted to provide a similar visualisation in usp_ssiscatalog for some time and today I have finally gotten around to implementing it. With this new release one can call up the executable stats using this query:

EXEC usp_ssiscatalog
      
@a                      =  
'exec'
  
,   @exec_execution         =  
0
  
,   @exec_events            =  
0
  
,   @exec_errors            =  
0
  
,   @exec_warnings          =  
0
  
,   @exec_executable_stats  =  
1

And the returned dataset will include a new column called Gannt. This screenshot demonstrates the value that this provides:

20130905gannt

On the left we have the execution_path of each executable and on the right is a bar indicating the start time and duration relative to all the other executables. Collectively these bars provide a nifty Gannt chart-alike view of your execution thus allowing you to easily identify which executables are taking up the most time in your executions.

In addition there is a similar view for the list of executions that is gotten by executing:

EXEC usp_ssiscatalog @a='execs'

That return a new column, relative_duration:

SNAGHTML16daf47b

which simply provides a bar for each execution indicating how long it took relative to all the other executions.

I hope these new visualisations in usp_ssiscatalog prove useful to you. If they do (or even if they don’t) please let me know, I love getting feedback and I don’t get nearly as much as I would like for SSIS Reporting Pack. Get the goods from SSIS Reporting Pack v1.1.1.0.

@Jamiet

Published Thursday, September 05, 2013 6:47 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jbooker said:

For the sake of feedback, albeit useless feedback...I love your visualizations.  I only wish a client were using 2012 project deployment model so could enjoy your reports.  Keep up the great work!

September 5, 2013 2:04 PM
 

mbourgon said:

Holy crapola!  How'd I miss that?  That rocks!

March 6, 2014 5:17 PM
 

jamiet said:

Thanks Michael, i like comments like that :)

March 6, 2014 5:29 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement