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

Constraint
FK_logSSISErrors_logSSISAppInstance_AppInstanceID
Foreign Key References
[log].SSISAppInstance(AppInstanceID)
,PkgInstanceID int Not Null
Constraint FK_logSSISErrors_logPkgInstance_PkgInstanceID
Foreign Key References
[log].SSISPkgInstance(PkgInstanceID)
,ErrorDateTime datetime Not Null
Constraint DF_logSSISErrors_ErrorDateTime
Default(GetDate())
,ErrorDescription varchar(max) Null
,SourceName varchar(255) Null
)
print 'Log.SSISErrors created'
end
Else
print 'Log.SSISErrors table already exists.'
print ''
/* log.LogError 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 = 'log'
And p.name = 'LogError')
begin
print 'Dropping log.LogError stored procedure'
Drop Procedure [log].LogError
print 'Log.LogError stored procedure dropped'
end
print 'Creating log.LogError stored procedure'
go
Create Procedure [log].LogError
@AppInstanceID int
,@PkgInstanceID int