Building an Auditing Process for SSIS - Part 2

In this post I'l continue where I left off last time with the configuration of an SSIS package which allows the population of an audit schema. 

We use this to support a DBA Dashboard showing the progress of ETL operations and as a method of documenting the package mechanism. I've found administering complex dynamic SSIS packages which use parameters and expressions can be time consuming to debug even for relatively simple issues. In my organisation we now use this audit schema to allow the rapid diagnosis of issues and poor performance.

In a future post I'll blog our DBA dashboard which links into this schema and provides us a snapshot of all SSIS processes in much greater detail than can be derived from SQL Agent errors.

Configuration

Each Package requires a connection manager. Keep this distinct from any other connection managers as a dedicated metadata connection. Configure the connection manager with the sufficient security to write to the audit schema in your chosen Utility database.

Control Flow Elements

The package logic should be wholly contained within a Sequence Container with a SQL task as a pre-requisite step which initiates the BatchLog and another SQL task as a final step which closes that Batch.

Initiate Audit - SQL Task General Pane

The SQL statement reads as follows: EXEC Audit.LogPackageStart @BatchLogID = ?,@PackageName = ?,@ExecutionInstanceID = ?,@MachineName = ?,@UserName = ?,@StartDatetime = ?,@PackageVersionGUID = ?,@VersionMajor = ?,@VersionMinor = ?,@VersionBuild = ?,@VersionComment = ?,@PackageGUID  ,@CreationDate = ?,@CreatedBy = ?

Initiate Audit - SQL Task Parameter Mapping Pane

Initiate Audit - SQL Task Result Set Pane

Staying with the Control Flow pane we move onto the configuration of the Audit Termination.

Terminate Audit - SQL Task General Pane

The SQL Statement reads as follows: EXEC Audit.LogPackageEnd @PackageLogID = ?, @BatchLogID = ?,@EndBatchAudit = ?

Terminate Audit - SQL Task Parameter Mapping Pane

As this post is getting long I'll break here as the configuration of the Control Flow pane is now complete. Next time we'll move onto configuration of the Event Handlers.