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

Listing A-8 . Creating cfg.AppPackages and cfg.AddSSISApplicationPackage
/* cfg.AppPackages table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'cfg'
And t.name = 'AppPackages')
begin
print 'Creating cfg.AppPackages table'
Create Table cfg.AppPackages
(
AppPackageID int identity(1,1)
Constraint PK_AppPackages
Primary Key Clustered
,ApplicationID int Not Null
Constraint
FK_cfgAppPackages_cfgApplications_ApplicationID
Foreign Key References
cfg.Applications(ApplicationID)
,PackageID int Not Null
Constraint FK_cfgAppPackages_cfgPackages_PackageID
Foreign Key References cfg.Packages(PackageID)
,ExecutionOrder int Null
)
print 'Cfg.AppPackages created'
end
Else
print 'Cfg.AppPackages table already exists.'
print ''
/* cfg.AddSSISApplicationPackage 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 = 'AddSSISApplicationPackage')