Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Store procedure in SQL Audit OnPostExecute, but it executes before finishing the dataflow in SSIS Expand / Collapse
Posted Tuesday, October 20, 2009 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 19, 2010 6:02 PM
Points: 4, Visits: 107
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
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
Post #806147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse