Home > Audit, Logging, SQL Server, SSIS, SSIS 2008, SSIS Tasks > Adding Audit information to you dataset using SSIS.

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.

Begin

You 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 .

cols

Click OK. All the records in your dataset will now have the selected audit types as the extra columns.

Dataset

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.

Advertisements
  1. September 22, 2009 at 1:59 pm

    Good idea!
    —————————————
    signature: imuran

  2. Anitha Natarajan
    October 5, 2010 at 1:29 pm

    Thanks :)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: