MSBI # 59 – SSIS # 23 – Data Flow Task # 9 – Audit Transformation Task- Detailed information & Explanation with example , Use & Properties

Hi friends

I have started explaining each Data flow task, i.e continuing from my last as well as link

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?

image_thumb111

Features of Audit Transformation Task

The following table describes the system variables that the Audit transformation can use

image_thumb30

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 !!

image

After dragging Audit task we can see too tip for more explanation

image

Following are available input for Audit Parameters in SSIS Package :

image

We are mostly using Audit transformation most of the time for Event handler as shown in following diagram for following various error handling

image

Various Properties for each Audit Transformation Task?

For audit transformation we have very less properties as shown :

image

Reference link For Audit Transformation Task

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: