Building an Auditing Process for SSIS - Part 1

An audit schema published in the excellent book SSIS Problem– Design – Solution allows the efficient capture of SSIS progress and errors into a simple SQL Server schema. We use this to support a DBA Dashboard showing the progress of ETL operations and as a method of documenting the package mechanism.

SSIS can be difficult to debug, particularly when packages are dynamically driven by variables and expressions or when sub packages are used, but this audit mechanism allows for easier identification of problems and performance bottlenecks.

The SQL Server schema

Attached here is the documentation for the SSIS PDS Audit schema plus a CREATE script. This schema should created in a Utility database or as has been done here in a separate schema with a database named DataWarehouse.

The SSIS Components

The SSIS tasks make use of the following variables. They need to be set up as shown below.

Name	DataType	Value
BatchLogID	Int32	0
PackageLogID	Int32	0
EndBatchAudit	Boolean	False
VariableValue	String	Unable to convert to string

Our standard SSIS Template has these variables and tasks already plumbed in so all new development should use that package as a starting point.

In Part 2 I'll show how your SSIS package should be configured.