I have tried to connect to Oracle DB using OLE DB Source ie <strong>Microsoft OLE DB Provider for Oracle</strong>. I am able to connect. But when I clicked on Preview button in OLE DB Source, I received a warning message before previewing.Please see below :
<img src="https://i.stack.imgur.com/VQnXE.jpg" alt="Oracle Source using OLE DB Provider for Oracle warning message">
I am able to preview except this warning message. Which provider should we have to use for connecting Oracle ? <strong>Microsoft OLE DB Provider for Oracle</strong> or <strong>Oracle Provider for OLE DB</strong> ?
To use the original provider, you simply need to specify the code page. I had the same issue. for the connections for Oracle, I had to set the AlwaysUseDefaultCodePage = True
See the image here: Data Flow Component Properties
For instructions on how to set this, look here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dc1a61f2-1ab8-4ed3-b85c-db6481800b50/
See Jamie Thomson's answer and the one after it. They speak of the always use default code page option and explain how to find it and how to set it.
You might also try the Attunity connector for Oracle. I have never used this one, but I have heard great things about it and it is supposed to be very fast: