I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.
I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.
The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!
PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!
Yosonu,
The warnings relating to code pages can be fixed if the warnings are appeaing in an OLE DB Source component by setting AlwaysUseDefaultCodePage=TRUE. Unfortunately there is no equivalent property for the LOOKUP component - I have requested that be changed here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127229 I would appreciate your clicking through and explaining why this would be useful to you.
Is it the caching of the data that is taking too long? In this case you ahve a number of options.
a) Use a different driver to get the data out of oracle. there are many options out there - all with differing performance. The persistent driver is a good place to start: http://blogs.conchango.com/jamiethomson/archive/2005/11/15/2393.aspx
b) Use a MERGE JOIN instead of a LOOKUP. This would be the first thing I would try, perhaps in conjunction with a different driver.
c) Try using Partial or no cache. I note you said this caused an error - what was the error?
-Jamie
|||If you have issues on oracle & SSIS, please drop me a mail at info@.persistentsys.com. Persistent has a high speed oracle load driver and also deep expertise on SSIS + Oracle/DB2/Sybase and can help you.|||
jamie,
do you know if anyone has tested the oracle's ole db provider for windows with ssis?
No comments:
Post a Comment