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

As
SELECT a.ApplicationName
,p.PackageFolder + p.PackageName As
PackagePath
,cp.StartDateTime
,DateDiff(ss,
cp.StartDateTime,
Coalesce(cp.EndDateTime,GetDate())) As RunSeconds
,cp.Status
,ai.AppInstanceID
,cp.PkgInstanceID
,p.PackageID
,p.PackageName
FROM log.SSISPkgInstance cp
Join cfg.AppPackages ap
on ap.AppPackageID = cp.AppPackageID
Join cfg.Packages p
on p.PackageID = ap.PackageID
Join log.SSISAppInstance ai
on ai.AppInstanceID = cp.AppInstanceID
Join cfg.Applications a
on a.ApplicationID = ap.ApplicationID
WHERE ai.AppInstanceID
= Coalesce(@AppInstanceID,ai.AppInstanceID)
And a.ApplicationID > 0
Order By cp.PkgInstanceID desc
go
print 'Rpt.ReturnPkgInstanceHeader stored procedure
created.'
print ''
In SSDT, add a new report named Package Instance just like you added the Ap-
plication Instance report. Make sure you use the
rpt.ReturnPkgInstanceHeader stored procedure. To get the Report Wizard to
recognize a query that expects parameters, you need to add default parameter values on
the Design the Query page. My Query String textbox reads as follows:
exec rpt.ReturnPkgInstanceHeader NULL