Loading data to Oracle using SSIS
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 .
May 14th, 2012 6:06am

In the past when I was stuck with SQL2008 standard I've used a script destination so that you can get at the full feature set of the driver. I've never used CozyRoc, but they have a destination control thyat may work for you: http://www.cozyroc.com/ssis/oracle-destinationChuck
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 7:43am

Thank you.
May 14th, 2012 11:52pm

I try to use Oracle destination from CozyRoc using ADO.NET connection but when I set the parameter it show error about This provider does not support direct table access. So I think I will try to use script destination instead. Could you please suggest me about that I don't have knowledge to coding in .NET. Thank
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 12:58am

Best bet is to buy yourself a .net book and start reading. Someone may decide to write your code for you here but if you can't understand it to support it or debug it then you'll be no better off than when you started.Chuck
May 15th, 2012 8:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics