Audit Transformation

The Audit transformation enables the data flow in a package to include data about the environment in which the package runs. For example, the name of the package, computer, and operator can be added to the   data flow. Microsoft SQL Server Integration Services includes system variables that provide this information. Audit Transformation is use the following system variables.

Ø  ExecutionInstanceGUID

Ø  PackageID

Ø  PackageName

Ø  VersionID

Ø  ExecutionStartTime

Ø  MachineName

Ø  UserName

Ø  TaskName

Ø  TaskId

Step by step example of Audit Transformation

In control flow take data flow task

Click on next. On Data flow page you select data flow source. I am using data flow source as excel soure.

In excel I am having some records and I want to insert the record in the database table or some other destination with information like (form where we get that data , who inserted that records and what time that record was inserted etc).

Make the connection.

Now I am taking Audit Transformation. Double click on that. Here you will get Audit Transformation Editor.

Select Audit Type Output column automatically generated. If you want to change it you can. According to your requirement you can select Audit. Click on ok.

Now you take the data flow destination. I am taking data flow destination as OLEDB Destination.

Make the connection to the database. If you have the same structured table in database then it’s ok otherwise click on new you will get structure of the table. Click on ok.

Make the mapping of source columns and destination columns.

Click ok.

Now execute the package.

Package executed successfully.

Now see in database.