Audit Transformation

The Audit Transformation in SSIS Data Flow Task purposed to fetch some of system

 variables besides data stream columns to create a log for data stream.

List of Audit types and description

Value	Variable Name	Description
0	ExecutionInstanceGUID	GUID that identifies the instance running
1	PackageID	Unique identifier of the package running
2	PackageName	Name of the package
3	VersionID	Version ID of the package
4	ExecutionStartTime	Time when the package is started
5	MachineName	Computer Name on which package running
6	UserName	User Name under which package running
7	TaskName	Name of the task which is running
8	TaskId	Unique identifier of the task running

Step 1:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.

It shows a bellow window

Step 2:

Go to a views and create a new project ( Short cut of New Project is Ctrl + Shift + N).

Step 3:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.

Step 4:

To drag and drop the Data Flow Task

Step 5: 

To edit the Data Flow Task then it open a Data Flow

Step 6:

To dag and drop the OLEDB source and edit it.

Step 7:

To give a OLEDB connection and select the table from given data base and select which columns are required.

Step 8:

Select table as [Person].[Address]

Step 9:

Drag and drop and Audit transformation and edit it.

Step 10:

Select Audit type is Package ID

Step 11:

Drag and drop data reader destination

Step 12:

Execute the task