Adding Audit information to you dataset using SSIS.
Adding audit information to your dataset such as user/ machine that modified the data, package that inserted data into your table etc,. might be required while performing an ETL task. In SSIS you can achieve this by using the transformation “AUDIT” available in DFTs. Inside the DFT you can add “audit transformation” after any other transformation as shown.
Y
ou need to select the “Audit type” of your choice like package name, package ID, user name etc and assign appropriate column name to each of these audit type .
Click OK. All the records in your dataset will now have the selected audit types as the extra columns.
In the above image you can see two new columns “executed_package_id” and “Execution_instanceID” added to the dataset. You can use these columns in the following transformations as well as push it into a DB table. Hence any thing that gets modified in your table through packages can be tracked.