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

/* rpt.ReturnAppInstanceHeader 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 = 'ReturnAppInstanceHeader')
begin
print 'Dropping rpt.ReturnAppInstanceHeader stored
procedure'
Drop Procedure rpt.ReturnAppInstanceHeader
print 'Rpt.ReturnAppInstanceHeader stored procedure
dropped'
end
print 'Creating rpt.ReturnAppInstanceHeader stored
procedure'
go
Create Procedure rpt.ReturnAppInstanceHeader
@ApplicationName varchar(255) = NULL
As
Select a.ApplicationID
,ap.AppInstanceID
,a.ApplicationName
,ap.StartDateTime
,DateDiff(ss,ap.StartDateTime,Coalesce(ap.EndDateTime,GetDate()))
As RunSeconds
,ap.Status
From log.SSISAppInstance ap
Join cfg.Applications a
On ap.ApplicationID = a.ApplicationID
Where a.ApplicationName
= Coalesce(@ApplicationName,a.ApplicationName)
And a.ApplicationID > 0
Order by AppInstanceID desc