Home > SSIS > Audit Transformation

Audit Transformation

Auditing is the very crucial part in the data management to increase the integrity of the data.

Audit transformation task gives us an option to enable the additional environmental data in the data flow.

In this article, I will be explaining how to use audit transformation in the SSIS package to add some system values to prepare the data for auditing purpose.


I have a situation, in which, data flows from different servers into a central data warehouse. Assume, sale data from different geographical locations into the data warehouse.

I need to know the source of each row in the data warehouse. Because, it will be more helpful in cross validation of summarized data.

Audit Transformation

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.


Step 1: Create a SSIS package and add a Data flow task. Go to Data Flow design surface add ADO.Net source and configure with a table you have in your database.

Step 2: Add Audit transformation task. Open the Audit Transformation Editor and select Audit types such as Machine name and User name.

Step 3: Add a union all task and connect Ado.net to Audit and Audit to Union all task. To see the output during execution just add a data viewer between Audit and Union all task.

Step 4: Execute package and view the result.

For more information read MSDN article.

Thanks for reading. Visit Facebook page SQLServerRider.com for the latest news.

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: