SSIS - The Audit Transformation

http://www.bimonkey.com/2009/05/the-audit-transformation/

The Audit Transformation

Posted by BI Monkey on Tuesday, May 19, 2009 · 4 Comments 

In this post i’m going to cover the Audit Transformation. The sample package can be found here for 2008 and here for 2005, read guidelines on use here.

Fig 1: The Audit Transformation

What is the Audit Transformation?

The Audit Transformation is a simple component that simply adds the values of certain System Variables as new columns (that you name) to the data flow. It allows for a single System Variable to be added as many times as you like. An example is below:

Fig 2: Column selection

These are the variables that are available:

• ExecutionInstanceGUID - The GUID that identifies the execution instance of the package.
• PackageID - The unique identifier of the package.
• PackageName - The package name.
• VersionID - The version of the package.
• ExecutionStartTime - The time the package started to run.
• MachineName - The computer name.
• UserName - The login name of the person who started the package.
• TaskName - The name of the Data Flow task with which the Audit transformation is associated.
• TaskId - The unique identifier of the Data Flow task.

The sample package demonstrates some of these columns in the Data Flow “1 > Audit Transformation”

When would you use the Audit Transformation?

The most likely scenario for using this component is in creating log entries or adding metadata to error traps. It does seem  a little redundant though as adding a new column with the value of a System Variable can just as easily be done within a Derived Column Transformation, which offers greater flexibility. So the short answer is, I probably wouldn’t use this transformation. In the sample package I have a demo of using the Derived Column Transformation to achieve the same goals as the Audit Transformation, in the Data Flow “2 > Derived Column”