If an error occurs in Oracle while the Oracle Upsert Destination is writing to the database, the component will attempt to rollback all changes and remove temporary tables that were in use. This can cause an Illegal ROLLBACK attempt to occur if Oracle believes the component is attempting to write to the temporary table while the rollback attempt is made. Typically, the error will present itself in SSIS as so:
[TF Oracle Upsert Destination ] Error: Oracle.DataAccess.Client.OracleException ORA-24795: Illegal ROLLBACK attempt made at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReleaseConnections(IDTSManagedComponentWrapper100 wrapper)
Note the ORA-00600 error. This error message is indiciative of the scenario described above occurring and can be resolved with the following method.
Within in Visual Studio/BIDS, open the Oracle Upsert Destination for editing. On the Advanced tab of the component, locate the ‘Turn off internal transactions option’. You will want to check this box before executing the package.
After this option has been enabled, you should be able to run the package and the error will be resolved.