SSIS Reporting Pack is a suite of reports that I maintain at http://ssisreportingpack.codeplex.com/ that provide visualisation over the SSIS Catalog in SQL Server 2012 and attempt to add value over the reports that ship in the box. Work on the reports has stalled (my last SSIS Reporting Pack blog post was on 4th September 2011) as I’ve had rather more important things going on my life of late however I have recently checked-in a fix that couldn’t really be delayed.

I discovered a problem with the Execution report that was causing the report to effectively hang, it was caused by this bit of SQL hidden away in the report definition:

[generated_executables] AS (
   SELECT  [new_executable].[execution_path],[new_executable].[parent_execution_path]
   FROM    (
           SELECT  [execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_close_square] + 1)
           ,       [parent_execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_open_square])
           FROM    (
                   SELECT  [execution_path]
                   ,       [char_index_open_square] = CHARINDEX('[',REVERSE([execution_path]),1)
                   ,       [char_index_close_square] = CHARINDEX(']',REVERSE([execution_path]),1)
                   ,       [length_exec_path] = LEN([execution_path])
                   FROM    [exec_stats] es
                   WHERE   execution_path LIKE '%\[%]%'  ESCAPE '\'
                   )AS [loop_iteration]
           ) AS [new_executable]
   GROUP   BY [new_executable].[execution_path],[new_executable].[parent_execution_path]
)

It was there because SSIS does not currently treat a loop iteration as an executable yet I figured there was still value in being able to view it as such – this SQL essentially “invents” new executables for those loop iterations; its what enabled the following visualisation:

where each of the three iterations of a For Each Loop called “FEL Loop over top performing regions” appear in the report. Unfortunately, as I alluded, this could under certain circumstances (most likely when there were many loop iterations) cause the report to hang as it waited for the results to be constructed and returned.

The change that I have made eradicates this generation of “fake” executables and thus produces this visualisation instead:

Notice that the three “children” of the For Each Loop are no longer the three iterations but actually the task (“EPT Call Data Export Package”) contained within that For Each Loop. The problem here is of course that there is no longer a visual distinction between those three iterations; I have instead made the full execution path viewable via a tooltip:

If you preferred the “old” way of presenting this information and are happy to put up with the performance degradation then I have kept the old version of the report hanging around in the reporting pack as “execution loop with iterations”

however none of the other reports link to it so you will have to browse to it manually if you want to use it. Please let me know if you ARE using it – I would be very interested to hear about your experiences.

The last change to make you aware of in the execution report is that by default I no longer show OnPreValidate or OnPostValidate messages as I consider them to be superfluous and only serve to clutter up the results.

If you want to put them back, well, its open source so go right ahead!

The latest release of SSIS Reporting Pack that contains all of these changes is v0.4 and can be downloaded from http://ssisreportingpack.codeplex.com/releases/view/88178

Feedback on all of the above changes would be very much appreciated.

@Jamiet