October 20, 2009 at 3:52 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply