Reporting on execution logs using Reporting Services

Reporting on execution logs using Reporting Services

Rate This
  • Comments 1

Microsoft published a set of canned reports for RS a while back. The pack includes some reports for Integration Services that give you execution results and statistics for your packages (if they are using SQL Server logging). The reports are very handy, and provide a good starting point if you want to create your own custom reports.

Here are some screen shots of runs I did recently (those of you familiar with Project REAL might recognize the package names).

Summary

Summary report

 

Analysis

Analysis

 

 

Log Details

Log details

 

 

 

 

I recently tried the reports out with SQL 2008, and was happy to see that everything still worked – with one additional step. Since the SSIS logging table name has been changed from sysdtslog90 (2005) to sysssislog (2008), you’ll need to either update the reports to use the new name, or create a View which maps to the new table.

CREATE VIEW [dbo].[sysdtslog90]
AS
    SELECT [id]
          ,[event]
          ,[computer]
          ,[operator]
          ,[source]
          ,[sourceid]
          ,[executionid]
          ,[starttime]
          ,[endtime]
          ,[datacode]
          ,[databytes]
          ,[message]
      FROM [dbo].[sysssislog]
Note, if you’re upgrading from 2005 to 2008, this view is created for you automatically in MSDB. You’ll still need to create it yourself if you’re logging to a separate database, however.
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Matt,

    I'm blowing the dust off the old blog post.  I'd like to thank you for mentioning MS's SSIS Reporting pack.  I used them as a source of producing some updated SSIS reports which give a dashboard feel similar to SQL Server 2012's Execution and Logging Reports.  Check it out over here....

    troywitthoeft.com/simple-ssis-package-monitoring-for-sql-server-2008-part-two

    - Troy Witthoeft

Page 1 of 1 (1 items)