Geeks With Blogs
Random Musing Putting it together one piece at a time...

Just a couple of weeks ago, I was tearing my hair out trying to meet a really tight timeline and at the same time trying to solve a technical issue which in my opinion is a total waste of time. If anyone of you had ever tried to make SSIS and Oracle work on a 64 bit machine, I am sure you know what I was referring to.

Fortunately we have people like Steve McHugh and Greg Galloway, who both wrote some excellent how-to articles addressing this issue. I've now grown so dependent on these articles (especially Greg Galloway's) that I'm ripping it off to put on my own blog site in case he takes the article down one day.

"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.

  1. Download the latest 32-bit drivers and install them. We would suggest Oracle10g Release 2 ODAC.
  2. 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.
  3. 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.
  4. 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.
  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.
    1. 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.)
    2. 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"
    3. 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.

    4. 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.
    5. Fix the shortcuts for other 32-bit applications that need to connect to Oracle (such as Toad) as outlined in step B above.
  6. 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.
  7. 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."

It also helps to know (from Steve's article) the following

  • Visual Studio (BIDS) is 32 bit
  • SQL Server Management Studio is 32 bit
  • dtexecui.exe is 32 bit
  • dtexec.exe comes in 32 bit and 64 bit
  • Oracle Provider comes in 32 bit and 64 bit
  • SQLAgent is 64 bit

If Greg or Steve is reading this post, thanks a lot for making the installation/configuration so easy. Good on ya!

Posted on Tuesday, January 22, 2008 9:37 AM Business Intelligence , Connected Systems | Back to top


Comments on this post: How to get Oracle Drivers to work on an x64 Box for SSIS

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
hello there,

My SSIS Package runs fine in 64- bit server when executed in the command line (manually) but when scheduled in a SQL job or run by SQL Server Management studio, it just fails, we had to scheduled it in a Windows schedule to run it daily. I reviewed your setup, it looks similar to ours.
Left by Ali K on Mar 18, 2008 11:11 AM

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
Hi,

Using the above process I can execute the SQL task using the connection manager, but I get teh error ORA-12154 when I try to use that connection on a oledb source component.

Any help is appreicated.

Thanks,
Left by Ravi on Apr 16, 2008 10:56 PM

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
Ali K,

You got that error as the runtime for command line execution and SQL job or SQL Server Management studio runtime is not the same. One being 32 bit runtime and the other being 64 bit.

I hope that helps.
Thanks
Left by Nestor on Apr 17, 2008 10:22 PM

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
Hi Ravi,

It seems like the problem is not with SSIS setup but your TNA setting to your Oracle server. Did you manage to connect to your Oracle service using a Oracle Client before trying to access it from the OLEDB source component?

You can try to switch between the Microsoft Oracle driver and the Oracle native driver as well.

Thanks.

Left by Nestor on Apr 17, 2008 10:25 PM

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
Hi All,

I have installed Oracle 10g client to acess the Oracle Database on 64 - bit machine and I'm able to connect it through the SQL Plus and through the command promt but , I am facing issues while connecting the Oracle Database from Bussiness Inteligence Environment(SSIS) using OLEDB source for Oracle

Is there any patch which can resolve the problem or any solution which will fix this issue at one shot.

Regards,
Left by Vivek Singh on Jul 25, 2008 4:15 AM

# OLE DB ERROR
Requesting Gravatar...

Hi,

I got the following error when extract data to SSIS Server using Visual Studio from remote Oracle DB.

I've installed both Oracle11g client ( 32-bit and 64-bit ) into SSIS Server. The connection test was successful using Visual Studio BUT fail to load the data into SSIS Server.

ERROR :
======

[Connection manager "xxxtcp.sksfrs"] Error: SSIS Error Code DTS_E_OLEDBERROR.

An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

I need help on the resolution. Thank you in advance for the reply.

Regard
Azahary
Left by azahary on Aug 20, 2009 5:59 AM

# re: How to get Oracle Drivers to work on an x64 Box for SSIS
Requesting Gravatar...
Thanks guys, keep up the good work. this helped me a lot.
Left by Vinitha on May 20, 2010 12:33 PM

Your comment:
 (will show your gravatar)


Copyright © Nestor | Powered by: GeeksWithBlogs.net