Store procedure in SQL Audit OnPostExecute, but it executes before finishing the dataflow in SSIS

• javieregh-756441

  SSC Rookie

  Points: 42

  More actions

  October 20, 2009 at 3:52 pm

  #205809

  I need to read the table sysssislog when it finishes because I want to make an update in my own audit table

  I have this store procedure in "SQL Audit OnPostExecute" (Project Real), but it executes before finishing the dataflow in SSIS:

  INSERT adm.Etlprocessaudit

  (etlbatchaudit_oid

  ,parent_process_oid

  ,process_nm

  ,process_guid

  ,version_guid

  ,version_build

  ,exec_start_dt

  ,exec_end_dt

  ,elapsed_time

  ,exec_user_id

  ,exec_parm_str

  ,success_ind

  ,return_code

  ,comments)

  SELECT a.etlbatchaudit_oid, NULL, source AS process_nm, b.sourceid AS process_guid, executionid AS version_guid, @version_build AS version_build, MIN(starttime)AS exec_start_dt , MAX(endtime) AS exec_end_dt,

  (SELECT convert(VARCHAR,Max(b.endtime) - MIN(b.starttime),114))AS elapsed_time,

  operator AS exec_user_id, NULL AS exec_parm_str

  ,success_ind = case

  when datacode = 0 then 'C' --Complete

  else success_ind

  end --case

  ,datacode AS return_code

  ,NULL AS comments

  FROM adm.Etlbatchaudit a, dbo.sysssislog b

  WHERE a.etlbatchaudit_oid = @etlbatchaudit_oid

  AND a.executionguid = b.executionid

  GROUP BY a.etlbatchaudit_oid, source, b.sourceid, executionid, operator,success_ind, datacode

  What I'm doing wrong or when I can put this store procedure after the update of the table sysssislog

  Thanks in Advanced

  Javier