Audit Transformation

SSIS's Audit Transformation is just a highly specific version of the derived column transformation.  It performs the same function - adding a new column to a data set.  However, it's limited to a few package and task-specific options, namely the following (The internal SSIS variable name is listed after each description):

Execution instance GUID - Insert the GUID that uniquely identifies the execution instance of the package.  Variable = @[System::ExecutionInstanceGUID].

Package ID - Insert the GUID that uniquely identifies the package.  Variable = @[System::PackageID].

Package name - Insert the package name.  Variable = @[System::PackageName].

Version ID - Insert the GUID that uniquely identifies the version of the package.  Variable = @[System::VersionGUID].

Execution start time - Insert the time at which package execution started.  Variable = @[System::StartTime].

Machine name - Insert the name of the computer on which the package was launched.  Variable = @[System::MachineName].

User name - Insert the login name of the user who launched the package.  Variable = @[System::UserName].

Task name - Insert the name of the Data Flow task with which the Audit transformation is associated.  Variable = @[System::TaskName].

Task ID - Insert the GUID that uniquely identifies the Data Flow task with which the Audit transformation is associated.  Variable = @[System::TaskID].

 

Step-by-Step:

1) Add a Data Flow Task to your package.

2) Add an OLE DB Source transformation.  Use any connection/table.

3) Add the audit transformation.   Configure the transformation by adding audit types, such as Package Name and Execution Start Time.   Make sure the output column names are sensible.  It's easy to mis-click and add the same audit type twice - SSIS won't change the name for you if you then correct the audit type.

 

4) Add a Destination or a union all transformation as a placeholder.  If you're using a union all, add a data viewer between the audit transformation and the union all so you can see the results.  Execute the package...

The audit transformation makes adding these columns slightly easier, as you don’t have to worry about the variable names, and SSIS auto-populates the column names.  But if you’re adding any other columns along with the auditing information, you’ll have to use a derived column transformation – which you can do in lieu of the audit transformation:

This derived column transformation produces the same results as the above audit transformation.  Additionally, there are many more system variables in SSIS than are covered by the audit transformation.  A complete list is located on MSDN.