1

I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS. It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the transfer had been realized with a Linked Server.

I do have the logic done and used the "Native OLE DB\Microsoft OLE DB Provider for Oracle" as well as the "Native OLE DB\Microsoft OLE DB Provider for Oracle" OLEDB connection.

When I run the package, the transfer of just 9,934 rows takes about a minute. Which for a table containing 28,228472 records is to long.

I tried using the "Oracle Source" component with an Oracle Connection Manager. While I can test the connection, as well as design the process and even preview the result set, as soon as I run it in my Visual Studio, it states the error: "The component metadata for could not be upgraded to the newer version of the component. The PerformUpgrade method failed."

The Visual Studio is: Visual Studio 2019 16.2.0

The Visual Studio project compatibility mode is: SQL Server 2016

The targeted SQL Server is: SQL Server 2016 (13.0.5233.0)

The package is supposed to be run on an SQL Server: SQL Server 2016 (13.0.5264.1)

The Oracle server version is: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production

Thank you for your help.

Improve this question
1

3 Answers 3

Reset to default
1

I managed to solve the issue of performance as well as making sure that the total amount data is transferred.

I dropped the use of the OLEDB connection in favor of "ADO NET". I used ".Net Providers\OracleClient Data Provider" for the source and ".Net Providers\SqlClient Data Provider" for the destination.

The ADO NET source and connection provide a consistent data transfer performance and does obtain all records. Transfers between SQL Server do still work better with an OLEDB connection. Transfers between ADO sources and OLEDB destinations require non-unicode to unicode conversions.

Improve this answer
0

My suggestion is don't use SSIS. I have found it to be notoriously slow on transfers from Oracle to MSSQL. I was once presented with an SSIS job that was taking in excess of 4 hours to move less than 1 million rows. I did an Oracle 10046 trace on the process and found it was doing row-by-row, slow-by-slow processing over a cursor. I wrote a PL/SQL procedure to do a simple INSERT INTO tablename@sqllvr VALUES (SELECT FROM oracle_table). It completed in less than 4 minutes, vs. the 4+ hours for the SSIS method. It's still running daily, several years later.

Improve this answer
-1

Pl use Attunity Connector which promises High Speed Data Transfer. we are using for almost 5 years now.

PS: Only till MS SQL 2016.

MS-SQL 2017, MS-SQL 2019 onwards Attunity does not support. ODAC will support for MS-SQL 2017.

MS-SQL 2019 onwards Microsoft has got add-on called Microsoft Connector.

Improve this answer
1
  • Thanks for your contribution; could you edit it to provide a little more detail on whether your installation was improved by using that.
    – Mark Stewart
    Dec 13, 2021 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.