Using SSIS as an ETL for Oracle
-
The powers that be have decided one of my teams larger databases would be less of a headache running on Oracle. Oracle is the norm at our organization so this is not surprising.
Porting the database objects and views over to an Oracle platform is a substantial task but not daunting. What I primarily would like to avoid is a complete rewrite of my ETL processes.
Has anyone had any success using SSIS as an ETL for Oracle? My real issue with it is the fact that the Oracle data provider is so incredibly slow. Has anyone found a way to optimize it? or anyone had luck with a third part provider?
-
I have been using SSIS with Oracle for awhile now.
If you are running on a 64 bit platform, you pretty much have no options, you need to use the native 64 bit Oracle OLEDB driver. It works pretty well, but it is not 100% OLEDB compliant. Because of this, it acts very strangely when dealing with numeric data types. I recently had an issue in which it decided to make any negative numbers zero without warning.
If you are using a 32 bit SSIS server, you can use the MS OLEDB driver for Oracle. It is much more compliant and only has a few issues - all of which generate actual errors. One of the bigger ones is not being able to handle inline comments (--) because it strips out line feeds (making everything after the comment on the same line that has been commended out).
I have not had major issues with the performance of either OLEDB provider since upgrading to the 10g client. Prior to that, everything was slow and very buggy.
-
We are using 10G R2, but seeing horrible performance when I try to use SSIS. I tried both the provider from Oracle and the Microsoft provider and didn't see much of an improvement either way.
I'm seeing speeds of 150 recs a sec on the simplist of transforms. Simply selecting the data from a two column table and inserting it in to another table is processing at 150 recs a sec. I can do this with a sql statement in oracle and process a million records in less than a sec.
any ideas?
-
I would first suspect a network traffic problem. Remember that when you run SSIS through BIDS, it pulls data from the server to your workstation and then back to the server. That is two or three machines involved in the process. There could be a bottleneck anywhere along the way.
There is absolutely something wrong. I am able to pull about a million records per second from an Oracle server using SSIS.
-
I am selecting from SQL server, and writing into Oracle. The performance is about 100 to 200 rows per sec. I have tried Oracle OLE db, and MSDAORA both. The SSIS package is stored on the SQL server, I connect to the server using remote desktop connectivity and execute the package.
What did you use to get a throughput of a million rows per sec?
Does SSIS allow to set a commit interval when writing into Oracle?
-
shailendra (3/26/2009)
I am selecting from SQL server, and writing into Oracle. The performance is about 100 to 200 rows per sec. I have tried Oracle OLE db, and MSDAORA both. The SSIS package is stored on the SQL server, I connect to the server using remote desktop connectivity and execute the package.What did you use to get a throughput of a million rows per sec?
Does SSIS allow to set a commit interval when writing into Oracle?
Michael says he is able read data from Oracle with that speed, not write. The standard OLE DB Destination is very slow. You can check CozyRoc Oracle Destination component. It uses the bulk-loading API and gets 10x-30x faster loading speed. You can check more information about it here.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply