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