I need to load the data from a Oracle server in another country by using SSIS package. I've configured Oracle drivers, environment variables and connections in SSDT already. I'm loading about 200 MB of data (one table) and it takes about 20 minutes! to load the data. The link in the remote location is 15Mbps with 86ms of Latency. Is there a way to configure the Oracle or SSIS package to speed up the load ?

  • 2
    Surely there is a way, but first you'll need to understand where most of that time is spent. How long does it take to run the query locally on the Oracle server? How long does it take to transfer a 200 MB file between the two sites? – mustaccio Apr 7 '17 at 16:02

You should get some significant performance improvement using the FetchSize connection string property. The Oracle documentation for the max value is here.

FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

But mainly I would recommend upgrading to SSIS Enterprise edition and using this connector. Here is the one for SSIS 2014 but you can search for other versions easily. The performance of that connector is much improved.

  • 1
    A couple of years ago I was faced with a similar issue. Developer put together SSIS to load oracle from MSSSQL. Load was taking five hours. I put an oracle 10046 trace on it and discovered that SSIS was fetching and inserting row-by-row (slow-by-slow). I wrote a simple PL/SQL procedure to a simple INSERT INTO local_table (SELECT columns FROM REMOTE_TABLE@MSSQL. The load took 4 minutes. – EdStevens Apr 11 '17 at 23:30
  • I've SQL 2014 Enterprise (SSIS Enterprise) already. I've downloaded a ODAC 3.0 connector - mentioned above - but as I tested ODAC 3.0 connector works twice time slower then Oracle ODBC instantclient_11_2. So Oracle ODBC drivers works faster. But still it takes 2 minutes to load 250k rows (8 columns) table from remote Oracle database. It takes 4 seconds to get the result from the Oracle database directly. – Tomasz Wieczorkowski Apr 14 '17 at 11:19
  • In addition I had to correct the System Enviromental Variables - "Path" and correct the path to ODAC 3.0 to work. From x64 path "c:\odac\odac64;c:\odac\odac64\bin;" to x86/32bit path "c:\odac\odac32;c:\odac\odac32\bin;" which will work properly from Visual Studio 2013.5 (which is 32 bit). – Tomasz Wieczorkowski Apr 14 '17 at 11:21
  • GregGalloway or someone else - could you tell me who to check and change the FetchSize connection string in SSIS ? I checked the Oracle Source properties in SSIS but there is no value like connection string there. – Tomasz Wieczorkowski Apr 14 '17 at 11:26
  • @TomaszWieczorkowski if you click on the connection manager and go to the Properties window there's a ConnectionString property. Add it there. – GregGalloway Apr 15 '17 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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