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.

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

Jennifer Fuss
  • 43
  • 10
  • So are you trying to transfer data to an SQL Server 2000, or to an SQL Server 2016 instance? Your question starts off with 2000 but ends with 2016. Of course 2000 has been out of support for a very long time now, so that's worth clarifying. – Jeroen Mostert Aug 20 '19 at 15:24
  • I am trying to transfer data from Oracle to an SQL Server 2016. To remove a process from an SQL Server 2000 to a supported system. I adapted the opening post to offer a more clear description. – Jennifer Fuss Aug 20 '19 at 15:27
  • We use "Native OLE DB\Microsoft OLE DB Provider for Oracle" to read from Oracle and "Native OLE DB\SQL Server Native Client 11.0" to write to SQL Server and we transfer around 13 million in 9 minutes. Make sure to check firewall and networking issues between your servers. Try writing the output into a local file (test the source), then uploading the file to the destination (test the destination), that is if the SSIS package is running in a 3rd server. – EzLo Aug 20 '19 at 15:32
  • What does the package do? – Tab Alleman Aug 20 '19 at 15:50
  • @Ezlo Thank you for the advice, I do will investigate further. Upon trying to write from a text file into the server, the performance was similarly slow. I will consult our infrastructe department.
    To answer your question, I do indeed work from a development server.     – Jennifer Fuss Aug 20 '19 at 16:21
  • @TabAlleman The package runs through the following steps:
    Truncate Table
    Data Source with:
    (SELECT [...] FROM [SCHEMA].[TABLE])
    Data Destination with a 1 to 1 match.     – Jennifer Fuss Aug 20 '19 at 16:22

2 Answers2

1

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

Adam Yan
  • 502
  • 2
  • 7
  • Thank you, that does indeed improve the speed drastically. I have had totally forgot to enable it. It slows down notably after the first million records, yet works way better. :) – Jennifer Fuss Aug 20 '19 at 16:26
  • Is there are possibilty to place effective chunks of data? My SSIS package worked faster up to 4.4 million rows (of 28 million), yet came to a halt at that mark. – Jennifer Fuss Aug 20 '19 at 16:42
  • check your memory usage while it is running, if it halts around 4GB for the SSIS process, then you may need to use partial cache mode. – Adam Yan Aug 20 '19 at 17:16
  • My transfer does currently look like this: https://imgur.com/H2ih54W With the structure, I don't see an option for a partial cache. If I insert a LookUp, the performance is back to its initial slowness. – Jennifer Fuss Aug 21 '19 at 08:47
  • Even stranger, and concerning, it stops at 4.4 million records, despite actually being supposed to return 28 million, all while just a fraction of the machines memory is being used: https://imgur.com/C7g2J8h – Jennifer Fuss Aug 21 '19 at 09:59
  • I don't see any part for treating the identical records, what is your plan for same row loaded 2nd time? – Adam Yan Aug 21 '19 at 12:14
  • The process truncates the temp table before it starts. I do admit that it is clunky to load all 28 million entries daily. So far I only tried to replicate the existing process and didn't look into the question whether there is a column that I can check to only include new or updated rows. – Jennifer Fuss Aug 21 '19 at 13:10
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 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.

Jennifer Fuss
  • 43
  • 10