Loading data to Oracle

I have a few about SSIS and need you to help . Currently I developed ETL package using SSIS to loading data from MSSQL Server to Oracle and between Oracle to Oracle.

I found that the performance is very poor (using 1 hour to load 1 M records) and the package failed when loading data at 3rd  M from 5 M records because of memory insufficient(Max setting for SSIS is 12 GB) . So I have to find new task to replace OLD DB Source/Destination that connect to Oracle and I found that Oracle Connection by Attunity is recommended by many developer on the internet so I change the connection from OLD DB Source/Destination to Oracle Connection by Attunity(using less than 10 minute to load 1M records).

But  after deploy the SSIS package to the production server I found that Oracle Connection by Attunity does not support in MSSQL 2008 Standard Edition so I need to remove the Oracle Connection by Attunity task and replace with the from OLD DB Source/Destination.

The constraint is the OLD DB Source/Destination performance is very poor, cannot meet the expectation in process time and fail when running a large data.

So please kindly advice me about configuration, connection setting of SSIS and Oracle to improve performance and eliminate memory insufficient issue . Your advice would be helpful, Thank you in advance .

When you created your OLEDB connection manager connection, did you use Microsoft OLE DB Provider for Oracle or the Oracle Provider for OLE DB? The Microsoft one is going to be slow(er), you should be using the native Oracle provider. This should appear (I believe) once you install and configure the Oracle client on the system.

The connection is Oracle Provider for OLE DB. I had try almost every connection but I Oracle Provider for OLE DB still run at very slow speed so I think that I may need to find new oracle destination task to improve speed. Any idea? Please advice.

Thank.

Hmm, well from my knowledge the "normal" providers and definitely slower and as you've already read Attunity is the way to go if you're looking for speed. You may need someone else to weigh in on this but this issue that's more familiar with this but this MIGHT be a case for you guys to go Enterprise Edition if performance and use of those components are critical to your infrastructure.

Thank you