Hi friends
I have started explaining each Data flow task, i.e continuing from my last as well as link
- MSBI # 55 – SSIS # 21 – Data Flow Task # 7 – All Transformation in Data Flow task
- MSBI # 57 – SSIS # 22 – Data Flow Task # 8 – Aggregate Transformation Task
In this article we are going to cover Audit Transformation with following points
- Why Audit Transformation Task is Use ?
- How Audit Transformation Task is look like ?
- Features of Audit Transformation Task
- How to use Audit Transformation Task ?
- What is alternative For Each Audit Transformation Task ?
- Various Properties For each Audit Transformation Task ?
- Reference link For Audit Transformation Task
Why Audit Transformation Task is Use?
Audit term itself defines this task is used for Audit Tracking of any package tasks.
This also enables the data flow in a package to include data about the environment in which the package runs.
We can configure the Audit transformation by providing the name of a new output column to add to the transformation output, and then mapping the system variable to the output column.
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
Audit Transformation task has a predefined system variable that we can use in the data flow. It behaves like derived column. It will add additional column(s) as you select the number of audit type.
How Audit Transformation Task is look like?
Features of Audit Transformation Task
The following table describes the system variables that the Audit transformation can use
We have Following variables to use audit transformation
- 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.
How to use Audit Transformation Task?
Drag Audit task from Data Flow Task , as shown in following screen shot !!
After dragging Audit task we can see too tip for more explanation
Following are available input for Audit Parameters in SSIS Package :
We are mostly using Audit transformation most of the time for Event handler as shown in following diagram for following various error handling
Various Properties for each Audit Transformation Task?
For audit transformation we have very less properties as shown :
Reference link For Audit Transformation Task
Hope this helps !!
Hope you have understood Audit Transformation Task in SSIS and ready to use every aspects for same
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
(B)usiness (I)ntelligence Mentalist 
7 thoughts on “MSBI # 59 – SSIS # 23 – Data Flow Task # 9 – Audit Transformation Task- Detailed information & Explanation with example , Use & Properties”