SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
javieregh-756441
javieregh-756441
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search