SSIS::::Using Audit Transformation

About Audit Transformation::

    The Audit Transformation is a simple component that simply adds the values of certain System Variables as new columns as you name to the data flow. It looks like as below:

Fig 1; The Audit Transformation

    It allows for a single System Variable to be added as many times as you like. Here is an EX:

 

Fig 2: Column selection

These are the variables that are available:

• ExecutionInstanceGUID - The GUID that identifies the execution instance of 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.

Above Example demonstrates some of these columns in the Data Flow Audit Transformation.

When would you use the Audit Transformation?

         General scenario for using this component is in creating log entries or adding metadata to error traps. I probably wouldn’t use this transformation. There is a Derived Column Transformation to achieve the same goals as the Audit Transformation with greater flexibility, in the Data Flow. we will discuss about this Derived Column transformation in the next posts.