Hello,
Database : SQL Server 7.0.
Provider : Microsoft OLE DB Provider for Oracle.
Using DTS I am creating a package which will export data from SQL Server tables to Oracle tables. The table structures in both databases is identical.
I wish to use transactions. If any problem arises and the data from particular table cannot be inserted to oracle table, then I want the entire transaction to rollback.
I have enabled the Use Transaction option for the DTS package. Also for the stepsI have checked on Join Transaction which will join the steps to the existing transactions.
However when I execute the package I get the following error.
“Task 1 does not support distributed transactions or failed to join”
I have tried using Oracle Provider for ODBC as well as Microsoft ODBC driver for Oracle, the error in both the cases was same.
Can any one suggest what could be done to use distributed transactions when transferring data from SQL to Oracle.
In the reverse scenario, transferring data from Oracle to SQL, using the Microsoft OLE DB Provider for SQL, everything works fine without a hitch.
Thanks & Regards,
Vinay