Can't get Oracle OLE DB provider to work from SSIS on a 64-bit environment

[Steen Schlüter Persson (DK)]

Hi

I posted this question in the .server newsgroup yesterday, but
apparently my cross-post to this newsgroup didn't made it, so now I post
it once more.

I'm having some troubles setting up an Oracle datasource on a SSIS
installation running 64-bit. We have it setup in our test/dev
environment running 32-bit where it works with Microsoft OLEdb Provider
for Oracle. Since this Provider isn't available in the 64-bit version I
was told to install the Oracle OLE DB provider client.
I did that and from Management Studio on that server, I can set up a
linked server using this provider and that works fine. When I then in
SSIS set up a connection using this Oracle OLE db provider and
specifying the same servername, I get an error when I test the
connection. The error is :

Test connection failed because of an error in initializing provider.
ORA-12154: TNS: kunne ikke fortolke den angivne
forbindelsesidentifikator (The last bit is in Danish and translated it
says something like 'Couldn't understand the actual connection
indicator').

If I look a the providers that are being used, I can see that the linked
server is using a provider called 'OraOLEDB.Oracle' where in SSIS it's
using a provider called 'OraOLEDB.Oracle.1'. This is most likely
because I first installed the regular Oracle client and then later on
when I figured out that I need the Oracle ODAC I installed that. I then
ended up with 2 Oracle "client" installations, so that is most likely
why I see 2 different providers. What then puzzles me, is how I can
choose which one to use. No matter what I do, it seems like the .1 is
chosen in SSIS. I've even tried to uninstall the "old" client but that
doesn't make any difference.
I'd love to uninstall both Oracle clients and then try just to install
the ODAC, but that isn't that simple. First of all, there are no way to
uninstall ODAC and when I uninstall the regular Oracle Client with the
installer, it still leaves a lot of files and registry entires around..:-(.

I've set up Oracle connections many times and feel I'm having a fair
grip of how it should be done, but this 64-bit /SSIS issue is driving me
nuts.....

Does any of you have any good suggestions on how to solve this issue?

[Russell Fields]

Steen,

I don't have a direct solution for your problem, but I have a suggestion.
If the 32-bit driver worked well for you, you can have your SSIS package run
in 32-bit mode.

The Books Online topic repeated here
http://msdn2.microsoft.com/en-us/library/ms141766.aspx explains how to run
a 32-bit SSIS job from a 64-bit SQL Server and SQL Agent. In part it says:
"select a job step type of Operating system, and enter a command line or use
a batch file that invokes the 32-bit version of dtexec.exe."

RLF

Hi
I posted this question in the .server newsgroup yesterday, but apparently
my cross-post to this newsgroup didn't made it, so now I post it once
more.
I'm having some troubles setting up an Oracle datasource on a SSIS
installation running 64-bit. We have it setup in our test/dev
environment running 32-bit where it works with Microsoft OLEdb Provider
for Oracle. Since this Provider isn't available in the 64-bit version I
was told to install the Oracle OLE DB provider client.
I did that and from Management Studio on that server, I can set up a
linked server using this provider and that works fine. When I then in
SSIS set up a connection using this Oracle OLE db provider and
specifying the same servername, I get an error when I test the
Test connection failed because of an error in initializing provider.
ORA-12154: TNS: kunne ikke fortolke den angivne
forbindelsesidentifikator (The last bit is in Danish and translated it
says something like 'Couldn't understand the actual connection
indicator').
If I look a the providers that are being used, I can see that the linked
server is using a provider called 'OraOLEDB.Oracle' where in SSIS it's
using a provider called 'OraOLEDB.Oracle.1'. This is most likely
because I first installed the regular Oracle client and then later on
when I figured out that I need the Oracle ODAC I installed that. I then
ended up with 2 Oracle "client" installations, so that is most likely
why I see 2 different providers. What then puzzles me, is how I can
choose which one to use. No matter what I do, it seems like the .1 is
chosen in SSIS. I've even tried to uninstall the "old" client but that
doesn't make any difference.
I'd love to uninstall both Oracle clients and then try just to install
the ODAC, but that isn't that simple. First of all, there are no way to
uninstall ODAC and when I uninstall the regular Oracle Client with the
installer, it still leaves a lot of files and registry entires
around..:-(.
I've set up Oracle connections many times and feel I'm having a fair
grip of how it should be done, but this 64-bit /SSIS issue is driving me
nuts.....
Does any of you have any good suggestions on how to solve this issue?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

[Steen Schlüter Persson (DK)]

Hi Russel,

Thanks for the link. I have been through that topic though and it
doesn't seems to help me a lot. The project I'm trying to get running,
consists of approx 25 seperate packages where 3-4 of them gets data from
different Oracle sources, but I'm not even got to the point where I'm
trying to run the whole lot.
So far I'm only testing the data sources set up, and here it fails
already when I try to edit a Data Source or create a new one and do a
"Test Connection".
There are two issues in the topic that could be an issue, and that is to
switch the whole project to 32-bit mode and then the part that says that
the Designer only shows 32-bit versions of the provider.
I have switched the project to "32-bit mode" but that didn't made any
difference. What I think I'll try now, it to install the 32-bit version
of the Oracle ODAC to see if that makes any difference. I'm not wuite
sure if that will invalidate the 64-bit version I've installed, but I'll
have to see..:-). If I can get that to work, then at least I know I'm on
right track and then I'll take it from there.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Post by Russell Fields
Steen,
I don't have a direct solution for your problem, but I have a suggestion.
If the 32-bit driver worked well for you, you can have your SSIS package run
in 32-bit mode.
The Books Online topic repeated here
http://msdn2.microsoft.com/en-us/library/ms141766.aspx explains how to run
"select a job step type of Operating system, and enter a command line or use
a batch file that invokes the 32-bit version of dtexec.exe."
RLF

Hi
I posted this question in the .server newsgroup yesterday, but apparently
my cross-post to this newsgroup didn't made it, so now I post it once
more.
I'm having some troubles setting up an Oracle datasource on a SSIS
installation running 64-bit. We have it setup in our test/dev
environment running 32-bit where it works with Microsoft OLEdb Provider
for Oracle. Since this Provider isn't available in the 64-bit version I
was told to install the Oracle OLE DB provider client.
I did that and from Management Studio on that server, I can set up a
linked server using this provider and that works fine. When I then in
SSIS set up a connection using this Oracle OLE db provider and
specifying the same servername, I get an error when I test the
Test connection failed because of an error in initializing provider.
ORA-12154: TNS: kunne ikke fortolke den angivne
forbindelsesidentifikator (The last bit is in Danish and translated it
says something like 'Couldn't understand the actual connection
indicator').
If I look a the providers that are being used, I can see that the linked
server is using a provider called 'OraOLEDB.Oracle' where in SSIS it's
using a provider called 'OraOLEDB.Oracle.1'. This is most likely
because I first installed the regular Oracle client and then later on
when I figured out that I need the Oracle ODAC I installed that. I then
ended up with 2 Oracle "client" installations, so that is most likely
why I see 2 different providers. What then puzzles me, is how I can
choose which one to use. No matter what I do, it seems like the .1 is
chosen in SSIS. I've even tried to uninstall the "old" client but that
doesn't make any difference.
I'd love to uninstall both Oracle clients and then try just to install
the ODAC, but that isn't that simple. First of all, there are no way to
uninstall ODAC and when I uninstall the regular Oracle Client with the
installer, it still leaves a lot of files and registry entires around..:-(.
I've set up Oracle connections many times and feel I'm having a fair
grip of how it should be done, but this 64-bit /SSIS issue is driving me
nuts.....
Does any of you have any good suggestions on how to solve this issue?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

[unknown]

The OraOLEDB issue on BIDS & SQL Agent under 64bit Windows system is kind of famous issue..

Recently I found the solution that works for me.
That is installing Oracle 32bit & 64bit both clients in the server what sit on 64bit Windows system.
I run SSIS, Linked Server, VB, C# and SQL Agent under 64bit SQL Server for some Oracle database interaction and all of them works fine!

You can find the exact steps in here.
http://knol.google.com/k/jeyong-park/accessing-oracle-data-source-from-64bit





Posted as a reply to:

Re: Can't get Oracle OLE DB provider to work from SSIS on a 64-bit environment
18-Apr-07

Hi Russel,

Thanks for the link. I have been through that topic though and it
doesn't seems to help me a lot. The project I'm trying to get running,
consists of approx 25 seperate packages where 3-4 of them gets data from
different Oracle sources, but I'm not even got to the point where I'm
trying to run the whole lot.
So far I'm only testing the data sources set up, and here it fails
already when I try to edit a Data Source or create a new one and do a
"Test Connection".
There are two issues in the topic that could be an issue, and that is to
switch the whole project to 32-bit mode and then the part that says that
the Designer only shows 32-bit versions of the provider.
I have switched the project to "32-bit mode" but that didn't made any
difference. What I think I'll try now, it to install the 32-bit version
of the Oracle ODAC to see if that makes any difference. I'm not wuite
sure if that will invalidate the 64-bit version I've installed, but I'll
have to see..:-). If I can get that to work, then at least I know I'm on
right track and then I'll take it from there.
--
Regards
Steen Schl?ter Persson
Database Administrator / System Administrator


Russell Fields wrote:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials/aspnet/ec832ac7-6e4c-4ea8-81ab-7374d3da3425/wpf-and-the-model-view-vi.aspx