2

I work with an OLAP sql server environment that relies on TSQL's openquery function for routine data integration from Oracle Data Warehouse.

It was a surprise to me that no other ETL tool was used, but the process works and has been in place for over a decade. In exploring better (or newer) practices, we benchmarked and tested using SSIS instead of openquery. Max buffer size and number of rows per buffer were both fine tuned for the table to be moved. Yet, SSIS did not benchmark higher than openquery - both performed similarly.

Some online research (may be outdated was a few years ago) suggested either using an affinity driver or tweaking a connection string property called FetchSize, but we have not followed up on these settings.

Besides the size of the data and the latency between server and client, what are the biggest factors that affect moving speeds? Is there something specialized I input for SSIS to speed up the oracle download?

Lee Y.
  • 119
  • 1
  • 1
  • 5

1 Answers1

2

SSIS works best with Attunity drivers when connected with Oracle. I've used SQL Server 2016 Enterprise and Attunity 4.0 which is compatible with SSIS of that version, works best.

To boost up performance, play with Batch size property of Attunity driver to match up your server load.

Further, 2016 onwards, SSIS comes with AutoAdjustBufferSize property, which works pretty well.

Prabhat G
  • 2,974
  • 1
  • 22
  • 31