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

A package instance consists of an application package ID, an application instance ID,
and an execution start time.
Let's start by creating the log.SSISPkgInstance table and stored procedures.
Listing A-15 contains these database objects.
Listing A-15 . Building the Package Instance Logging Table and Stored Procedures
/* log.SSISPkgInstance 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 = 'log'
And t.name = 'SSISPkgInstance')
begin
print 'Creating log.SSISPkgInstance table'
Create Table [log].SSISPkgInstance
(
PkgInstanceID int identity(1,1)
Constraint PK_SSISPkgInstance Primary Key Clustered
,AppInstanceID int Not Null
Constraint
FK_logSSISPkgInstance_logSSISAppInstance_AppInstanceID
Foreign Key References
[log].SSISAppInstance(AppInstanceID)
,AppPackageID int Not Null
Constraint
FK_logSSISPkgInstance_cfgAppPackages_AppPackageID
Foreign Key References
cfg.AppPackages(AppPackageID)
,StartDateTime datetime Not Null
Constraint DF_cfgSSISPkgInstance_StartDateTime
Default(GetDate())
,EndDateTime datetime Null
,[Status] varchar(12) Null
)
print 'Log.SSISPkgInstance created'
end