SSIS: Making the Most of sysssislog

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

5 thoughts on “SSIS: Making the Most of sysssislog

  1. I see that rows from the sysssislog table get automatically deleted when my job is running. Although I am not manually doing that in my package, the rows get deleted. Is there a limit on the number of rows that are being stored or does it depend on the size of msdb. The current size of the msdb is only 35 MB. Can you please advise

    1. Is your sysssislog table in msdb ? If so, I would recommend creating the sysssislog table in the control database instead – the database that’s used by your ETL framework. SSIS log provider for SQL Server only requires a connection string to database and it could be your control database. The table will be created the first time your package runs with logging enabled.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s