February 17, 2011 at 7:36 am
I have built a package on my workstation that transfers data from an Oracle 10G database to a SQL Server 20005 (on Windows 2003 x64). I have tested the package and all is good. I am using the Native Oracle OLEDB driver. As part of the planned deployment to the server I have run into some issues. Upon research I have found a lot of articles about people having problems with running x64 and oracle oledb connections. My questions are as follows:
1. Can you run an OLEDB connection to Oracle on an x64 SQL SSIS? If so what are the steps to configure? I find it hard to believe that its really not supported.
2. If OLEDB is not supported with x64 what is the best manner to make a connection? Is it using the command line x86 version of SSIS or some other driver/method?
3. What should be installed on the SQL Server (minimal) from the Oracle install package to provide the connectivity required?
February 17, 2011 at 8:29 am
I searched for "oledb 64-bit sql" online, and found a bunch of articles that seem to suggest you need the Oracle 10G client and that you need to include the path to it in your environment paths.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2011 at 8:48 am
GSquared (2/17/2011)
I searched for "oledb 64-bit sql" online, and found a bunch of articles that seem to suggest you need the Oracle 10G client and that you need to include the path to it in your environment paths.
I use the oracle 10g client with no issues on x64 servers. GSquared is correct in that you need to install the client, and not just an oledb driver.
You can also use what they call the Instant Client, which is a much smaller install. It's fairly easy to find if you google it (make sure you get x64). You will also need to at the server to the tnsnames.ora file, and then reboot the server once for it to work.
February 17, 2011 at 9:30 am
I have the 10G x64 Client install. I did a custom install and specified the drivers and windows interfaces and did a reboot. So maybe the issue is with the need for the client (or instaclient).
Do you know if as GSquared mentioned you need to set a path variable?
February 17, 2011 at 9:41 am
I did an install of the insta client. Will reboot tonight to test. But I dont have a network/admin folder for the tnsnames.ora file. Any idea where this should reside with instaclient as a search of the machine shows no tnsnames file.
Thanks!
February 17, 2011 at 9:50 am
dmc-608719 (2/17/2011)
I did an install of the insta client. Will reboot tonight to test. But I dont have a network/admin folder for the tnsnames.ora file. Any idea where this should reside with instaclient as a search of the machine shows no tnsnames file.Thanks!
Sorry..with IC you need to set an environmental variable in windows to point to tnsnames and then create/copy one. It's a one time thing per server that I haven't had to do in years...
Check here for more details: http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1169739823
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply