CODEWITHSUNDEEP
sql-serveroracle-databasevisual-studiossisssis-connection-manager
Jennifer Fuss
Jennifer Fuss

Reputation: 43

Performance issues of a data transfer between Oracle and SQL Server using SSIS

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.

The transfer I designed in SSIS process is slow.

I tried using 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.

The result is, that it takes about a minute to transfer 9,934 records of a data set that spans 28,228472 rows.

I tried using the "Oracle Source" component with an "Oracle Connection Manager".

The result is, I can test the connection, as well as design the process and even preview the result set. As soon as I run the package 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."

I need to be able to transfer the date in a reasonable amount of time.
The environment I am using is as follows:

Visual Studio: Visual Studio 2019 16.2.0
SSIS project compatibility mode is: SQL Server 2016

Targeted SQL Server: SQL Server 2016 (13.0.5233.0)
The SSIS package will be run on: SQL Server 2016 (13.0.5264.1)

The Oracle environment is:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Upvotes: 1

Views: 3224

Answers (2)

Jennifer Fuss
Jennifer Fuss

Reputation: 43

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 a "ADO NET" one. 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.

Upvotes: 1

Adam Yan
Adam Yan

Reputation: 502

I would suggest to use "Table or view - Fast load" mode for your oledb destination.

And use a "upsert" data flow with lookup transform to minimize data writing I/O.

If possible, use "Full cache" as lookup mode (RAM demanding).

Here are some other tips that might help.

enter image description here

Upvotes: 1

Related Questions