Audit logging in SSIS

• vipin_jha123

  Hall of Fame

  Points: 3085

  More actions

  May 4, 2015 at 2:48 am

  #303591

  Hi,

  I ma having a package having 10 DFT Task.

  In Each DFT Task there are odd number of insert and update at the end

  for example.

  source>lookup>lookup1>lookup2>transformation>conditionalsplit>

  1 New CurrYearMinus4YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 4) && ISNULL(Run_Id)

  2 New CurrYearMinus3YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 3) && ISNULL(Run_Id)

  3 New CurrYearMinus2YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 2) && ISNULL(Run_Id)

  4 New CurrYearMinus1YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 1) && ISNULL(Run_Id)

  5 New CurrYearYEAR(ScheduleDepartDate) == @[User::CurrentYear] && ISNULL(Run_Id)

  6 Update CurrYearMinus4YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 4) && Run_Id > 0

  7 Update CurrYearMinus3YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 3) && Run_Id > 0

  8 Update CurrYearMinus2YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 2) && Run_Id > 0

  9 Update CurrYearMinus1YEAR(ScheduleDepartDate) == (@[User::CurrentYear] - 1) && Run_Id > 0

  10 Update CurrYearYEAR(ScheduleDepartDate) == @[User::CurrentYear] && Run_Id > 0

  as you can see that data gets splitted and inserted and updated into different tables.

  in above example data gets inserted into 5 tables and update don same.

  Now I want to log how many records gets inserted and updated on each tables.

  I would like to have logging for each table loading.

  TableNameStartTimeEndTimeInsertedUpdated Status

  like this I have 10 DFT on my control flow.

  requesting you to suggest the best way to do the same.

  regards,

  Vipin jha