May 14, 2007 at 8:48 am
I am copying data from an ORACLE database using the Microsoft OLE-DB provider for Oracle. About once a month the package gets "stuck", possibly due to a lock on the Oracle side. Normally the step completes in 10 seconds; when stuck it can continue executing, doing nothing, for many days until I stop it.
How can I make the step fail if it does not complete in a specified short period of time?
Thank you for any assistance.
May 14, 2007 at 11:31 am
I can think of 2 solutions.
1. If this is a scheduled job try this approach:
Add a step to you DTS that writes the start time to table before firing off the connection to Oracle.
Add a step when the Oracle job completes that writes the end time to the table.
Create a 2nd DTS that is scheduled to start 1 minute after the first one.
It simply checks to see it the end time exists.
If the end time exists then then DTS 2 ends. If the End time doesn't exist have it issue sp_stop_job
<jobName>.
2. Try the same process using global variables. Task 1 sets the start time. Task 2 starts the Oracle connection. Task 3 starts a timer function that checks to see if 60 seconds has passsed since the starttime.
If not keep counting, if yes set the pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=True. On successful completion of Task 2 use task 4 to kill task 3 just as above.
HTH
Regards,
Matt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply