December 21, 2015 at 4:13 pm
Hi All,
Need your help in below scenario.
We have SSIS packages which involves Oracle and SQL servers. We have logic like when data is transferred to Oracle from SQL it sets flag Y on SQL end.
But when package fails during execution with error, flag doesn't updates successfully and hence results in unique constraints issue while next run.
Is there any option from where we can rollback transaction in SSIS involving Oracle and SQL Server.
December 22, 2015 at 6:19 am
Sushant Yadav (12/21/2015)
Hi All,Need your help in below scenario.
We have SSIS packages which involves Oracle and SQL servers. We have logic like when data is transferred to Oracle from SQL it sets flag Y on SQL end.
But when package fails during execution with error, flag doesn't updates successfully and hence results in unique constraints issue while next run.
Is there any option from where we can rollback transaction in SSIS involving Oracle and SQL Server.
You should, of course, not update the flag until after the transfer is successful. I suspect that your problem is that the transfer was partially successful, but then failed (and therefore did not update the flag on SQL for the rows which were transferred), would that be the case?
One solution is to adjust what happens on the Oracle side so that inserts of rows which already exist are handled (via an UPDATE/MERGE, or ignored).
Another is to change your logic such that the commit on the Oracle side is a single transaction.
Yet another is to modify your ETL so that step 1 is a 'clean-up' which looks for and updates any rows in SQL Server which should have the 'Y' flag and do not.
Creating some sort of single distributed transaction across both databases sounds impractical to me.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply