Audit Transformation in SSIS allows you to add extra columns with auditing information (Machine name, package name, id, date etc) required in auditing. For instance, If we want to check
- Which user executed this package?
- Execution time of the package?
- From which machine he/she executed?
- What is the task ID, PackageID, Name of the Package etc?
Then we can use this SSIS audit transformation to store that information in the destination table.
List of available options in Audit Transformation in SSIS :
- ExecutionInstanceGUID – The GUID that identifies the execution instance of the package.
- PackageID – This is the unique identifier of the package.
- PackageName – Shows the name of the package.
- VersionID – The unique version number of the package.
- ExecutionStartTime – The time the package started to run.
- MachineName – The Name of the computer.
- UserName – The login name of the person who started the package.
- TaskName – Name of the Data Flow task with which the Audit transformation is associated.
- TaskId – The unique identifier of the Data Flow task.
Audit Transformation in SSIS Example
For this example, We are using Adventure Works DW in our SQL Server
STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it to Audit transformation.
Double click on it will open data flow tab.
STEP 2: Drag and drop OLE DB Source, Audit transformation, ADO.NET destination on to the data flow region
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.
For the time being we selected firstname, lastname, yearlyIncome from Adventure Works data base and we want to send the same to the destination table with extra audit information.
SQL Command Text we used is:
SELECT FirstName, LastName, YearlyIncome FROM DimCustomer
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
TIP: If we don’t want any column then there is no point to add it in to your SQL command.
STEP 5: Click ok and connect the green arrow of OLE DB Source to Audit Transformation. Double click on the AUDIT Transformation to edit it
Select the required Audit Types and click ok. Here, we are selecting all the audit types.
NOTE: You can change the Column name as per your requirement by editing the Output Column Name field.
STEP 6: Next we have to provide Server, database and table details of the destination. So double-click on the ADO.NET Destination and provide the required information
From the above screenshot you can observe, We are storing the audit transformation data in the [AUDIT Destination] table present inside the SSIS Tutorial database.
STEP 7: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
NOTE: If your input column names and destination column names are same, the intelligence will automatically map. if there is any changes in the column names (any alias columns or any calculated columns) then we have map them manually.
Clicking OK to finish configuring Audit Transformation in ssis package. Let us run the package and see
Let us open the SQL Management Studio Query window to Preview the data
NOTE: The Audit Transformation in SSIS doesn’t support error output.
Thank you for Visiting Our Blog