Home > SSIS Best Practices > SSIS – Auditing Strategy Through Package Event Handlers

SSIS – Auditing Strategy Through Package Event Handlers

Events in SSIS can be scoped to the entire package, a container, or a granular and an individual task.

For the purpose of the current ETL project I am working on, I am using a mix of the following approach for auditing:

1) Single SQL table tblamETLAudit. This table will contain a mix of processing results, and errors if the execution failed. This table is shared by all the packages. The middle part can be further normalized for storing processing results.

image

 

2) I am using three different values for the column EntryType, START, END and OnError. The START and END entries are two simple Execute SQL Tasks that Insert data into the above SQL table.

image image

Here is an SQL example for inserting data into the table.

  • Note: Insert statement can be constructed in the Expression Builder without parameter mapping. I personally do not like the challenge of managing the double and single quotation marks.

Mapping the system and user variables.

image

Insert data into the table, with ?s representing parameters.

image

3) The OnError entry is obtained from the OnError event handler that is scoped to the entire package.

Here is an example.

image

image

image

4) Finally, I need to report from the SQL audit table. I’ve created a separate package for this purpose.

This package has 3 tasks. The first task reads from the SQL table and gets the query result in XML. The second task transforms the XML into HTML with a XSLT file. The last script task sends out the HTML report to the recipients though a SMTP mail server.

image 

Here is the audit email report from a typical day:

image

Categories: SSIS Best Practices

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

%d bloggers like this: