Reporting on execution logs using Reporting Services

Published Mar 25 2019 02:09 PM 88 Views
Not applicable
First published on MSDN on Jun 23, 2008

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




Analysis





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]
<br/><br/>.csharpcode, .csharpcode pre<br/>{<br/> font-size: small;<br/> color: black;<br/> font-family: consolas, "Courier New", courier, monospace;<br/> background-color: #ffffff;<br/> /*white-space: pre;*/<br/>}<br/>.csharpcode pre { margin: 0em; }<br/>.csharpcode .rem { color: #008000; }<br/>.csharpcode .kwrd { color: #0000ff; }<br/>.csharpcode .str { color: #006080; }<br/>.csharpcode .op { color: #0000c0; }<br/>.csharpcode .preproc { color: #cc6633; }<br/>.csharpcode .asp { background-color: #ffff00; }<br/>.csharpcode .html { color: #800000; }<br/>.csharpcode .attr { color: #ff0000; }<br/>.csharpcode .alt <br/>{<br/> background-color: #f4f4f4;<br/> width: 100%;<br/> margin: 0em;<br/>}<br/>.csharpcode .lnum { color: #606060; }
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.
%3CLINGO-SUB%20id%3D%22lingo-sub-387446%22%20slang%3D%22en-US%22%3EReporting%20on%20execution%20logs%20using%20Reporting%20Services%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-387446%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jun%2023%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EMicrosoft%20published%20a%20%3CA%20href%3D%22http%3A%2F%2Fwww.microsoft.com%2Fdownloads%2Fdetails.aspx%3Ffamilyid%3DD81722CE-408C-4FB6-A429-2A7ECD62F674%26amp%3Bdisplaylang%3Den%22%20mce_href%3D%22http%3A%2F%2Fwww.microsoft.com%2Fdownloads%2Fdetails.aspx%3Ffamilyid%3DD81722CE-408C-4FB6-A429-2A7ECD62F674%26amp%3Bdisplaylang%3Den%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20set%20of%20canned%20reports%20for%20RS%20%3C%2FA%3E%20a%20while%20back.%20The%20pack%20includes%20some%20%3CA%20href%3D%22http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Fc%2Fc%2Fb%2Fccbae9a4-1677-45d5-bf69-1a6c471940c9%2FSSISEventLogReportPack.exe%22%20mce_href%3D%22http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Fc%2Fc%2Fb%2Fccbae9a4-1677-45d5-bf69-1a6c471940c9%2FSSISEventLogReportPack.exe%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20reports%20for%20Integration%20Services%20%3C%2FA%3E%20that%20give%20you%20execution%20results%20and%20statistics%20for%20your%20packages%20(if%20they%20are%20using%20SQL%20Server%20logging).%20The%20reports%20are%20very%20handy%2C%20and%20provide%20a%20good%20starting%20point%20if%20you%20want%20to%20create%20your%20own%20custom%20reports.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20are%20some%20screen%20shots%20of%20runs%20I%20did%20recently%20(those%20of%20you%20familiar%20with%20%3CA%20href%3D%22http%3A%2F%2Fwww.microsoft.com%2Ftechnet%2Fprodtechnol%2Fsql%2F2005%2Fprojreal.mspx%22%20mce_href%3D%22http%3A%2F%2Fwww.microsoft.com%2Ftechnet%2Fprodtechnol%2Fsql%2F2005%2Fprojreal.mspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Project%20REAL%20%3C%2FA%3E%20might%20recognize%20the%20package%20names).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Summary%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99390i2C4C3B88CC3194E9%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Analysis%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99391iBE08414BF99FBEB8%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Log%20Details%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99392i5BE8CFB2BC3D3264%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EI%20recently%20tried%20the%20reports%20out%20with%20SQL%202008%2C%20and%20was%20happy%20to%20see%20that%20everything%20still%20worked%20%E2%80%93%20with%20one%20additional%20step.%20Since%20the%20SSIS%20logging%20table%20name%20has%20been%20changed%20from%20%3CSTRONG%3E%20sysdtslog90%20%3C%2FSTRONG%3E%20(2005)%20to%20%3CSTRONG%3E%20sysssislog%20%3C%2FSTRONG%3E%20(2008)%2C%20you%E2%80%99ll%20need%20to%20either%20update%20the%20reports%20to%20use%20the%20new%20name%2C%20or%20create%20a%20View%20which%20maps%20to%20the%20new%20table.%3C%2FP%3ECREATE%20VIEW%20%5Bdbo%5D.%5Bsysdtslog90%5D%20%3CBR%20%2F%3E%20AS%20%3CBR%20%2F%3E%20SELECT%20%5Bid%5D%20%3CBR%20%2F%3E%20%2C%5Bevent%5D%20%3CBR%20%2F%3E%20%2C%5Bcomputer%5D%20%3CBR%20%2F%3E%20%2C%5Boperator%5D%20%3CBR%20%2F%3E%20%2C%5Bsource%5D%20%3CBR%20%2F%3E%20%2C%5Bsourceid%5D%20%3CBR%20%2F%3E%20%2C%5Bexecutionid%5D%20%3CBR%20%2F%3E%20%2C%5Bstarttime%5D%20%3CBR%20%2F%3E%20%2C%5Bendtime%5D%20%3CBR%20%2F%3E%20%2C%5Bdatacode%5D%20%3CBR%20%2F%3E%20%2C%5Bdatabytes%5D%20%3CBR%20%2F%3E%20%2C%5Bmessage%5D%20%3CBR%20%2F%3E%20FROM%20%5Bdbo%5D.%5Bsysssislog%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E.csharpcode%2C%20.csharpcode%20pre%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%20font-size%3A%20small%3B%3CBR%20%2F%3E%20color%3A%20black%3B%3CBR%20%2F%3E%20font-family%3A%20consolas%2C%20%22Courier%20New%22%2C%20courier%2C%20monospace%3B%3CBR%20%2F%3E%20background-color%3A%20%23ffffff%3B%3CBR%20%2F%3E%20%2F*white-space%3A%20pre%3B*%2F%3CBR%20%2F%3E%7D%3CBR%20%2F%3E.csharpcode%20pre%20%7B%20margin%3A%200em%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.rem%20%7B%20color%3A%20%23008000%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.kwrd%20%7B%20color%3A%20%230000ff%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.str%20%7B%20color%3A%20%23006080%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.op%20%7B%20color%3A%20%230000c0%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.preproc%20%7B%20color%3A%20%23cc6633%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.asp%20%7B%20background-color%3A%20%23ffff00%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.html%20%7B%20color%3A%20%23800000%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.attr%20%7B%20color%3A%20%23ff0000%3B%20%7D%3CBR%20%2F%3E.csharpcode%20.alt%20%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%20background-color%3A%20%23f4f4f4%3B%3CBR%20%2F%3E%20width%3A%20100%25%3B%3CBR%20%2F%3E%20margin%3A%200em%3B%3CBR%20%2F%3E%7D%3CBR%20%2F%3E.csharpcode%20.lnum%20%7B%20color%3A%20%23606060%3B%20%7D%20%3CBR%20%2F%3E%20Note%2C%20if%20you%E2%80%99re%20upgrading%20from%202005%20to%202008%2C%20this%20view%20is%20created%20for%20you%20automatically%20in%20MSDB.%20You%E2%80%99ll%20still%20need%20to%20create%20it%20yourself%20if%20you%E2%80%99re%20logging%20to%20a%20separate%20database%2C%20however.%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-387446%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jun%2023%2C%202008%20Microsoft%20published%20a%20set%20of%20canned%20reports%20for%20RS%20a%20while%20back.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-387446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekatmai%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ereporting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 25 2019 02:09 PM
Updated by: