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, Subjectvarchar(10), Mark int, ExecutionStartTime DATETIME, UserNameNVARCHAR(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
5. After execution of the package, records will look like this.
