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

metadata we want for a given SSIS application. Listing A-10 contains the T-SQL data
definition language (DDL) script to build such a stored procedure named
cfg.GetSSISApplication .
Listing A-10 . Creating the cfg.GetSSISApplication Stored Procedure
/* cfg.GetSSISApplication 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 = 'cfg'
And p.name = 'GetSSISApplication')
begin
print 'Dropping cfg.GetSSISApplication stored procedure'
Drop Procedure cfg.GetSSISApplication
print 'Cfg.GetSSISApplication stored procedure dropped'
end
print 'Creating cfg.GetSSISApplication stored procedure'
go
Create Procedure cfg.GetSSISApplication
@ApplicationName varchar(255)
As
Select p.PackageFolder + p.PackageName As PackagePath
, ap.ExecutionOrder
, p.PackageName
, p.PackageFolder
, ap.AppPackageID
From cfg.AppPackages ap
Inner Join cfg.Packages p on p.PackageID = ap.PackageID
Inner Join cfg.Applications a on a.ApplicationID
= ap.ApplicationID
Where ApplicationName = @ApplicationName
Order By ap.ExecutionOrder
go