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
Hi Benny,
I am trying to implement logging in my package. Is it ok to create the sysssislog table before hand (before executing the package). Also the sp_ssis_addlogentry seems to use the sysssislog under the dbo schema only regardless of what account the package is running under.
Is this how it works?
Kavitha, It’s ok to create sysssislog table before hand. Just make sure the same column names and data types are used and it can exist only in dbo schema.
I recently authored a query which was inspired by some of the views you had listed here. Take a look and let me know what you think!
http://troywitthoeft.com/simple-ssis-package-monitoring-for-sql-server-2008/
hi Troy, glad you have taken the concept forward. Looks good.
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
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.