Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 571

Figure A-29 . Displaying an error
Quickly isolating the source of an error in an SSIS package is one way to improve
overall operational efficiency. These reports, working in tandem, facilitate efficient root
cause analysis.
The Events report is very similar to the Errors report. The T-SQL script for creating
the rpt.ReturnEvents " stored procedure is shown in Listing A-25 .
Listing A-25 . Building the rpt.ReturnEvents Stored Procedure
/* rpt.ReturnEvents stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnEvents')
begin
print 'Dropping rpt.ReturnEvents stored procedure'
Drop Procedure rpt.ReturnEvents
print 'Rpt.ReturnEvents stored procedure dropped'
end
print 'Creating rpt.ReturnEvents stored procedure'
go
Create Procedure rpt.ReturnEvents
@AppInstanceID int
,@PkgInstanceID int = NULL
As
Select
a.ApplicationName
,p.PackageName
,ev.SourceName
,ev.EventDateTime