SSIS Oracle

Question that comes up fairly often is about connecting to Oracle. Here is a wiki entry we wrote about doing it in a 32 bit/64bit environment

Here it is verbatim:

Best Article found so far
 
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11
 
Possible quick solution
 
Post done by
SRIRAM RAJAMANURI
 
Pasted from <http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f>
 
 
Or according to this version 11 sorts it all out anyway
http://blogs.msdn.com/debarchan/archive/2009/02/04/good-old-connectivity-issue.aspx
 
 
 
 
 
Oracle Driver Considerations
 
[Oracle 10g Release 2 ODAC x86]
[Oracle 10g Release 2 ODAC x64] 

 
To successfully connect to an Oracle data source an Oracle Ole DB driver must first be installed and the environment must be set up so that the driver can locate the TNSNAMES.ORA file. Oracle Ole DB drivers are installed as part of the  Oracle Data Access Components (ODAC)
 
Production Environment
Setup for production is fair simple. As our environment is 64bit, only the x64 ODAC is required. 
 
Installation Instructions:
1.        Install the 64 bit drivers (ODAC x64)
2.       Add a TNS_ADMIN environment variable and set its value to "C:\TNS"
a.       Right Click Computer -> Properties .
b.      Click on Advanced TAB
c.       Click Environment Variables Button
d.      Under System Variables Click New
e.      Set Variables name to "TNS_ADMIN"
f.        Set Variable value to "C:\TNS"
3.       Copy the TNSNAMES.ORA file to "C:\TNS"
 
The production environment is now ready to connect to Oracle data sources using the Oracle Ole DB driver.
 
Development Environment
For the purposes of this entry, Development Environment is an environment where packages are been developed and run on a 64 bit machine.
 
Background to Problem
Visual Studio 2008 and SQL Server Management Studio 2008 are 32 bit environments. As such at design time (or while using wizards or ui's) the a 32bit Oracle driver is needed. However, on a 64bit installation of windows all 32 bit applications are stored in "c:\program files (x86)\etc…" and the Oracle Data Provider throws an exception when ever called by a process that orginates from a folder that has parenthesis like (x86).
 
Installation Instructions:
1.       Install the 32 bit drivers (ODAC x86)
 
2.       Install the 64 bit drivers (ODAC x64)
 
3.       Add a TNS_ADMIN environment variable and set its value to "C:\TNS"
a.       Right Click Computer -> Properties .
b.      Click on Advanced TAB
c.       Click Environment Variables Button
d.      Under System Variables Click New
e.      Set Variables name to "TNS_ADMIN"
f.        Set Variable value to "C:\TNS"
4.       Copy the  TNSNAMES.ORA file to "C:\TNS"
 
5.       All 32-bit SQL Server applications start under the “Program Files (x86)” directory. You need to fix them so they start in the “Progra~2” directory. The following steps will need to be rerun after every subsequent SQL service pack is installed.
a.       The shortcuts to SQL Server Management Studio and SQL Server Business Intelligence Development Studio need to be fixed. Unfortunately, you can’t just change the shortcut, because Windows automatically expands the “Progra~2”. Instead, create a bat file that launches the EXE, then change the shortcut to point to the bat file. These bat files and shortcuts are included in the ZIP file below. Basically, those bat files look like:
start /B "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"
b.      When you double-click a .sln file, the path it uses for Visual Studio needs to be fixed.  Open Explorer... Go to the Tools menu… Folder Options… Flip to the File Types tab… Type in SLN to skip down to the SLN file type… Click the Advanced button… Highlight the “Open” action… Click Edit… Change the path to say:
"C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe" "%1"
Consider fixing any other file extensions you wish to double click which should launch 32-bit processes.
 
c.       Fix the PATH environment variable by changing any reference that says “C:\Program Files (x86)\Microsoft SQL Server\” to “C:\Progra~2\Microsoft SQL Server\”. And change any reference to “C:\Program Files (x86)\Microsoft Visual Studio 9.0” to “C:\Progra~2\Microsoft Visual Studio 9.0”. Environment variables can be edited by right clicking Computer -> Properties, clicking on the Advanced tab, clicking the Environment Variables button, choosing the Path system variable, and clicking Edit.
 
6.       Lastly, when building SSIS packages you will have to set the AlwaysUseDefaultCodePageproperty to True on the OLE DB Source components in your data flow tasks that pull from Oracle.