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

print ''
/* cfg.Packages 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 = 'Packages')
begin
print 'Creating cfg.Packages table'
Create Table cfg.Packages
(
PackageID int identity(1,1)
Constraint PK_Packages
Primary Key Clustered
,PackageFolder varchar(255) Not Null
,PackageName varchar(255) Not Null
)
print 'Cfg.Packages created'
end
Else
print 'Cfg.Packages table already exists.'
print ''
The script in Listing A-3 creates a schema named cfg if one doesn't already exist;
it then creates a table named cfg.Packages , which contains three columns:
• PackageID is an identity column that serves as the primary key.
• PackageFolder is a VarChar(255) column that holds the path to
the folder containing the SSIS package.
• PackageName is a VarChar(255) column that contains the name
of the SSIS package.
I recently began identifying the stored procedures, functions, and views that sup-
port such a repository as a database programming interface (DPI), and not as an applic-
ation programming interface, or API, because databases are not applications. Here, you