SSIS 2008 - Audit Transformation

Audit Transformation:

Audit transformation allows you to add information about the environment in which your package is running. The information provided by this transformation is taken from the system variables which SSIS provides. Listed below are the system variables which this transformation uses:

System Variable	Description
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.

This transformation does not provide Error routing.

Below shown is the Audit Transformation editor screenshot: 

In this we can either keep the default ‘Output Column Name’ or we can type our custom name.

Uses of Audit Transformation:

1. Logging: This transformation can be used for Logging purpose.

2. Security: Let’s say we have a package ‘Refresh_Master_Table.dtsx’, the purpose of this package is to refresh is the Master table which is used in most of our packages. For this package we want to implement a security mechanism, in which only user named ‘Admin’ can execute this package. To implement this functionality we can use ‘Username’ to get the current user who is executing this package, further in the package we can add conditional split in which can check if Username == ‘Admin’, if it equals then only the rest of the package functionality should execute or else nothing.