Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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
(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
Post #806147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse