Really want to get this down before I forget and hopefully this helps someone out there.
Issue: Connecting to an Oracle 10gR1 (can be other versions of 10 and 11 server) source from SSIS, development on Windows 7 x64 bit environment.
Solution: You will have to download the Oracle 11gR2 32-bit client from Oracle web site, do a custom install to only install the OLE DB provider and .NET provider.
Remember that if you want to run the SSIS package on a SQL Server 64-bit server, you will have to download the Oracle 11gR2 64-bit client and install that wherever the SSIS pkg will run.
In SSIS, choose the "Oracle provider for OLE DB", make sure you specify the Oracle servername, port and service name like this in "server or file name":
servername:1521/servicename
This way you DO NOT need to configure tnsnames.ora file. I repeat, you do not have to use archaic methods of configuring connection string.
Additional issue(s): My PATH environment variable is longer than 1023 characters due to many SQL versions. During Oracle client installation which complains about the max length of PATH variable, just ignore it, Windows 7 can handle more that 1023 chars in PATH var.
