SSIS Audit Transformation

SSIS Audit Transformation

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 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 this Wiki article.

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Zoltán Horváth edited Original. Comment: small formatting

Page 1 of 1 (1 items)