The default SSIS log provider for SQL Server captures package trace in sysssislog table. It does capture some useful but limited trace information about the package execution. This trace is of little use without user defined variable values like record count, status flags, control metadata, selection from and to dates etc.

Of course SSIS provides a framework to write custom log providers to handle specific situations like these. At the time of writing this post, the custom log providers are unable to access the package variables. The good news is significant improvements are anticipated in SSIS logging capabilities in next version of SQL Server.

For those of you on SQL Server 2008 R2 and below, you could still customize SSIS log by creating your own execution log table and using it in tandem with sysssislog. Your execution log table could be structured something as shown below. Note that the execution log table captures record count, status flags, control metadata, selection from and to dates etc.

CREATE TABLE dbo.EXECUTION_LOG

(

EXEC_LOG_ID Int IDENTITY NOT NULL,

--Control Metadata

EXEC_ID Int NOT NULL,

RUN_ID Char(5) NOT NULL,

STEP_ID Char(3) NOT NULL,

STEP_EXEC_STATUS Char(1) NULL,

--Start and End Dates

STEP_EXEC_START_DATETIME Datetime NULL,

STEP_EXEC_END_DATETIME Datetime NULL,

--Variables

VAR1 Varchar(100) NULL,

VAR2 Varchar(100) NULL,

VAR3 Varchar(100) NULL,

--Counts

REC_COUNT Int NULL,

ERR_COUNT Int NULL

--IMPORTANT LINK TO sysssisLog

Executionid Uniqueidentifier NULL,

CREATED_BY Varchar(100) NOT NULL,

CREATED_DATETIME Datetime NOT NULL)

Go

ALTER TABLE dbo.EXECUTION_LOG ADD CONSTRAINT PK_EXECUTION_LOG PRIMARY KEY (EXEC_LOG_ID)

Go

This execution log table could be populated at significant events (like onPostExecute and onError) or conditionally in the control flow. The column that joins your execution log table and sysssislog table is Executionid. The Executionid column stores GUID of the execution instance of the SSIS package and is automatically captured in sysssislog when logging is enabled. This value can be captured in your execution log from the system variable ExecutionInstanceGUID (Screen Capture 1)

Screen Capture 1 – Executionid (ExecutionInstanceGUID)

By combining your execution log with sysssislog, you could take advantage of SSIS logging capabilities and customize it for your specific logging requirements.

Benny Austin

Related Links

Simple SSIS package monitoring for SQL Server 2008

https://twitter.com/#!/vadimloginov/status/189724416324804608