SSIS Audit Transformation

  • Article

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

In some cases the ETL process is required to be audited. This means that data must contain when it was transformed, on which server, and who started the process. The Audit transformation is possible to these tasks.

A small hint: the functionality of the Audit transformation is a subset of the functionality of [[articles:SSIS Derived Column Transformation|Derived Column transformation]], which is also able to deliver the same. The main difference is that no input column values can be changed with this transformation, only new columns can be added. Here is the full list which system variables can be mapped to the new columns:

  • ExecutionInstanceGUID
  • PackageID
  • PackageName
  • VersionID
  • ExecutionStartTime
  • MachineName
  • UserName
  • TaskName
  • TaskId

 

The Audit Transformation Editor: all variables mapped to a new column

Unlike Derived Column, the Audit transformation does not support error outputs, it only has an input an an output.
It is a row (synchronous) transformation: each output row is in a 1:1 relation with an input row.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check [[articles:SSIS: List of Transformations|this Wiki article]].