Audit Transformation

- The Audit transformation lets us add columns that contain information about the package execution to the data flow.
- These audit columns can be stored in the data destination and used to determine when the package was run, where it was run, and so forth
- The following information can be placed in audit columns:
--> Execution instance GUID (a globally unique identifier for a given execution of the package)
--> Execution start time
--> Machine name
--> Package ID (a GUID for the package)
--> Package name
--> Task ID (a GUID for the data flow task)
--> Task name (the name of the data flow task)
--> User name
--> Version ID

EXAMPLE

Pre-requisite
Following script has to be executed in DB

CREATE TABLE AggregateDemo (ADID INT IDENTITY(1,1), StudentID INT, Subject varchar(10), Mark int)

GO

insert into AggregateDemo (StudentID,Subject,Mark)

values (1,'Maths',100),

(1,'Science',100),

(1,'English',90),

(2,'Maths',99),

(2,'Science',99),

(2,'English',95),

(3,'Maths',95),

(3,'Science',100),

(3,'English',98)

GO

CREATE TABLE AuditDemo (ADID INT, StudentID INT, Subject varchar(10), Mark int, ExecutionStartTime DATETIME, UserName NVARCHAR(50), MachineName NVARCHAR(50))

GO

Steps

1. Add Data Flow Task in Control Flow
2. Drag source in DFT and it should point to AggregateDemo
3. Add Audit Transformation and connect it with Source

4. Add Destination and map columns
5. After execution of the package, records will look like this.