SSIS Reporting Pack – a performance tip

jamiet — Mon, 05 Nov 2012 22:36:41 GMT

SSIS Reporting Pack is a suite of open source SQL Server Reporting Services (SSRS) reports that provide additional insight into the SQL Server Integration Services (SSIS) 2012 Catalog. You can read more about SSIS Reporting Pack here on my blog or had over to the home page for the project at http://ssisreportingpack.codeplex.com/.

After having used SSRS Reporting Pack on a real project for a few months now I have come to realise that if you have any sizeable data volumes in [SSISDB] then the reports in SSIS Reporting Pack will suffer from chronic performance problems – I have seen the “execution” report take upwards of 30minutes to return data. To combat this I highly recommend that you create an index on the [SSISDB].[internal].[event_messages].[operation_id] & [SSISDB].[internal].[operation_messages].[operation_id] fields. Phil Brammer has experienced similar problems himself and has since made it easy for the rest of us by preparing some scripts to create the indexes that he recommends and he has shared those scripts via his blog at http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip. If you are using SSIS Reporting Pack, or even if you are simply querying [SSISDB], I highly recommend that you download Phil’s scripts and test them out on your own SSIS Catalog(s).

Those indexes will not solve all problems but they will make some of your reports run quicker. I am working on some further enhancements that should further improve the performance of the reports. Watch this space.

@Jamiet

Convert an integer seconds value into words in VBScript : Reporting Services

jamiet — Sun, 14 Nov 2010 22:36:00 GMT

Occasionally I stick random bits of code up on this blog because (a) I know I’ll need it again someday and (b) maybe it’ll be useful for someone else. This particular VBScript function called TimeInWords can be used in SQL Server Reporting Services to turn a numerical value representing number of seconds into a meaningful string. For example:

TimeInWords(53) returns “53s”
TimeInWords(1632) returns “27m 12s”
TimeInWords(7923) returns “2h 12m 3s”
TimeInWords(894823) returns “10d 8h 34m 43s”

Pretty useful for reporting on durations. I haven’t done endless testing on it but I’m pretty sure it works. Here’s the code:

Function TimeInWords (RemainderTimeInSeconds as Integer) AS String
    Dim ReturnValue As String
    IF (RemainderTimeInSeconds > 86400)
        ReturnValue = TimeInDays(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 3600)
        ReturnValue = TimeInHours(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 60)
        ReturnValue = TimeInMinutes(RemainderTimeInSeconds )
    ELSE
        ReturnValue = TimeInSeconds(RemainderTimeInSeconds )
    End If
    Return ReturnValue
End Function

Function TimeInDays (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 86400)) + "d " + TimeInHours(RemainderTimeInSeconds Mod 86400)
End Function
Function TimeInHours (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 3600)) + "h " + TimeInMinutes(RemainderTimeInSeconds Mod 3600)
End Function
Function TimeInMinutes (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 60)) + "m " + TimeInSeconds(RemainderTimeInSeconds Mod 60)
End Function
Function TimeInSeconds (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(RemainderTimeInSeconds) + "s"
End Function

Nothing particularly special but if it saves someone else from writing it well…why not!

Hope that is useful to someone.

@Jamiet

Search results matching tags 'SSRS' and 'SQL Server Reporting Services'

SSIS Reporting Pack – a performance tip

Convert an integer seconds value into words in VBScript : Reporting Services