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

Event logging is very similar to error logging in SSIS. Part of the reason is that
SSIS reuses the object model for the OnError event handler in the OnInformation event
handler.
Let's begin by adding another table and stored procedure to the SSISConfig
database. The T-SQL script in Listing A-17 accomplishes this task.
Listing A-17 . Building the Event Logging Table and Stored Procedure
/* log.SSISEvents 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 = 'SSISEvents')
begin
print 'Creating log.SSISEvents table'
Create Table [log].SSISEvents
(
ID int identity(1,1)
Constraint PK_SSISEvents Primary Key Clustered
,AppInstanceID int Not Null
Constraint
FK_logSSISEvents_logSSISAppInstance_AppInstanceID
Foreign Key References
[log].SSISAppInstance(AppInstanceID)
,PkgInstanceID int Not Null
Constraint
FK_logSSISEvents_logPkgInstance_PkgInstanceID
Foreign Key References
[log].SSISPkgInstance(PkgInstanceID)
,EventDateTime datetime Not Null
Constraint DF_logSSISEvents_ErrorDateTime
Default(GetDate())
,EventDescription varchar(max) Null
,SourceName varchar(255) Null
)
print 'Log.SSISEvents created'