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

should begin building the SSISConfig DPI with a stored procedure to load data into
the cfg.Packages table, as shown in Listing A-4 .
Listing A-4 . The cfg.AddSSISPackages Stored Procedure
/* cfg.AddSSISPackage 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 = 'AddSSISPackage')
begin
print 'Dropping cfg.AddSSISPackage stored procedure'
Drop Procedure cfg.AddSSISPackage
print 'Cfg.AddSSISPackage stored procedure dropped'
end
print 'Creating cfg.AddSSISPackage stored procedure'
print ''
go
Create Procedure cfg.AddSSISPackage
@PackageName varchar(255)
,@PackageFolder varchar(255)
,@PkgID int output
As
Set NoCount On
declare @tbl table (PkgID int)
If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
Insert Into cfg.Packages
(PackageName