SSIS - Logging audit information

• vipin_jha123

  Hall of Fame

  Points: 3085

  More actions

  May 5, 2015 at 1:05 am

  #300424

  Hi,

  I am looking for storing the audit information in a seperate table, so that troubleshooting gets easier, in case of any issues in future. This is what I am planning to do.

  Create an even handler to be invoked at OnPostExecute. This event will invoke a SQL task. The SQL Task would insert the following information into the Audit_Log table.

  1) Data Flow Task Name - System::TaskName

  2) Package Name - System::PackageName

  3) Executing Date - GETDATE()

  4) # Input Rows - Name of the variable, that points to the Row count transformation immediately after the Source.

  5) # Target Insert Rows - Name of the variable, that points to the Row count transformation immediately before the target (where data is inserted).

  6) # Target Update Rows - Name of the variable, that points to the Row count transformation immediately before the target (where data is updated).

  7) Load Status - Where to get this information?

  8) Duration - DATEDIFF([System::ContainerStartTime] , GETDATE()]

  A similar kind of logging will be done at the package level, which will contain the following information:

  1) Package Name - System::PackageName

  2) Executing Date - GETDATE()

  3) Load Status - Where to get this information?

  4) Duration - DATEDIFF([System::StartTime] , GETDATE()]

  As I have multiple OLEDB Destination and OLEDB Command.

  I want to log in below format

  TableNameStartTimeEndTimeInsertedUpdated Status

  I am looking for a nod from the experts who have been helping me out the past few days, regarding my approach for logging audit information.

  Thanks in advance

  Regards,

  Vipin jha