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

Building the reports in this fashion makes sense. The Application Instance report
becomes a “gateway” for the Package Instance report—a “dashboard,” if you will.
More in a bit …
Let's turn our attention to the error log data. To retrieve it, use the T-SQL script
shown in Listing A-24 .
Listing A-24 . Building the rpt.ReturnErrors Stored Procedure
/* rpt.ReturnErrors 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 = 'ReturnErrors')
begin
print 'Dropping rpt.ReturnErrors stored procedure'
Drop Procedure rpt.ReturnErrors
print 'Rpt.ReturnErrors stored procedure dropped'
end
print 'Creating rpt.ReturnErrors stored procedure'
go
Create Procedure rpt.ReturnErrors
@AppInstanceID int
,@PkgInstanceID int = NULL
As
Select
a.ApplicationName
,p.PackageName
,er.SourceName
,er.ErrorDateTime
,er.ErrorDescription
From log.SSISErrors er
Join log.SSISAppInstance ai
On ai.AppInstanceID = er.AppInstanceID
Join cfg.Applications a