SSIS Slow Data Transfer between Oracle and SQL Server 2016

Question

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.

Answer 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.

Answer 2

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.

Answer 3

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.