As Steve McHugh describes very well, getting Oracle drivers to work on an x64 box for use with Integration Services 2005 development is a challenge. We are indebted to Steve for explaining the underlying problems, and you should review his article before proceeding. The following instructions are, we feel, an improvement over Steve’s instructions for the best way to get Oracle drivers working on an x64 box on which you wish to develop packages.
If all you want to do is run scheduled packages under the x64 version of SSIS, you can just do step 2. If you want to develop packages on this box or run the SQL Server Import/Export Wizard, then you need to do all the steps.
- Download the latest 32-bit drivers and install them. We would suggest Oracle10g Release 2 ODAC.
- Download the latest 64-bit drivers and install them. We would suggest Oracle10g Release 2 ODAC (64-bit) 10.2.0.3 for Windows x64.
- Because you have several drivers installed, we suggest you add a TNS_ADMIN environment variable which says “C:\TNS” and then move your tnsnames.ora and sqlnet.ora files to that one “C:\TNS” directory. You can add a TNS_ADMIN environment variable by right clicking My Computer on your desktop, choosing Properties, flipping to the Advanced tab, clicking the Environment Variables button, and adding that variable.
- Note: During one install of the drivers, it failed because it couldn’t find gacutil.exe. If you get that failure, uninstall the Oracle driver you just tried to install, then copy gacutil.exe and gacutil.exe.config from <C:\program files\Microsoft Visual Studio 8\SDK\v2.0\Bin> to <C:\program files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin>. The most recent time we did the installs, this was not a problem, so maybe Oracle has fixed this issue.
- 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.
- To fix the SQL Server Import/Export Wizard so you can launch it from Management Studio, fix the reference to it in the registry by running the “SQL import-export wizard on 64-bit server.reg” file that I have included in the ZIP file below. (That registry file fixes the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTS\Setup\WizardPath registry path.)
- 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 8\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"
- When you double-click a .sln file, the path it uses for Visual Studio needs to be fixed. If you only have Visual Studio 2005 (i.e. SQL Server 2005) installed on this server, you can make the following change. Open the C drive… 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 8\Common7\IDE\devenv.exe" "%1"
Consider fixing any other file extensions you wish to double click which should launch 32-bit processes.
- 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 8” to “C:\Progra~2\Microsoft Visual Studio 8”. Environment variables can be edited by right clicking My Computer on your desktop, choosing Properties, flipping to the Advanced tab, clicking the Environment Variables button, choosing the Path system variable, and clicking Edit.
- Fix the shortcuts for other 32-bit applications that need to connect to Oracle (such as Toad) as outlined in step B above.
- We had trouble when our master SSIS package launched child packages out-of-process if those child packages needed to connect to Oracle. We decided to change those Execute Package Tasks to run in-process, and we didn’t investigate further.
- You will probably have to set the AlwaysUseDefaultCodePage property to True on the OLE DB Source components in your data flow tasks that pull from Oracle.
Download a oracle x64 fix.zip which contains a couple of shortcuts and the .reg file used in step 5a above.
Miscellaneous Note: To setup a linked server to Oracle, review this post by Max Oleznyuk.