Connecting to Oracle with SSIS on x64

Discussion:

(too old to reply)

Mike L

2005-11-04 16:31:03 UTC

Hi all,

I've recently started the migration of a SQL 2000 database (32-bit) on
Windows 2000 Server to SQL 2005 64-bit on Windows 2003 Server (64-bit). The
databases migrate with no problems. However, now I'm trying to migrate our
DTS packages to SSIS and I have a couple of issues.

The first one is that most of our DTS packages pull data from an Oracle
Database (9.2) but everytime I try to connect to Oracle via SSIS it fails.
I've got the Oracle 9.2 client installed and can connect to Oracle via
SQL*Net just fine. The error in the SQLNet.log file is:

Fatal NI connect error 6413, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=force)(PORT=1515))(CONNECT_DATA=(SID=PRDL)(CID=(PROGRAM=C:\Program
Files (x86)\Microsoft Visual Studio
8\Common7\IDE\devenv.exe)(HOST=ELROND)(USER=webadmin))))

I looked up the error and I believe it has to do with the parentheses in the
"Program Files (x86)" argument. Apparently Oracle considers the parentheses
special characters and will fail to connect if it sees them. I don't know
how to fix it though! I uninstalled the SSIS client tools and tried to
reinstall them in a different folder but the install doesn't allow you to
change the location of the Business Intelligence Studio. Any suggestions?

The second issue is more clarification than an actual problem. Our server
is 64-bit but our development boxes are 32-bit (WinXP SP2). Will we have any
problems developing our SSIS packages on a 32-bit environment and then
deploying to 64-bit?

Any and all help is appreciated!

Mike

Mike L

2005-11-08 16:23:03 UTC

Permalink

Just an update. I installed the 64-bit 10.2 Oracle Client just to make sure
that didn't fix it. No luck. I then wrote a small .NET 2.0x64 Win Forms app
that connects to our Oracle instance with no problems. I think that
sufficiently verifies that the problem lies with SSIS.

I also did a complete re-install of SQL 2005 to see if it would let me
change the folder for the Business Intelligence Studio but it
didn't....*sigh* You can change the common tools folder but the Business
Intelligence Studio always goes in C:\Program Files (x86)\Microsoft Visual
Studio 8\...

I'm going to give Microsoft Support a call today. Wish me luck.

Mike

Post by Mike L
Hi all,
I've recently started the migration of a SQL 2000 database (32-bit) on
Windows 2000 Server to SQL 2005 64-bit on Windows 2003 Server (64-bit). The
databases migrate with no problems. However, now I'm trying to migrate our
DTS packages to SSIS and I have a couple of issues.
The first one is that most of our DTS packages pull data from an Oracle
Database (9.2) but everytime I try to connect to Oracle via SSIS it fails.
I've got the Oracle 9.2 client installed and can connect to Oracle via
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=force)(PORT=1515))(CONNECT_DATA=(SID=PRDL)(CID=(PROGRAM=C:\Program
Files (x86)\Microsoft Visual Studio
8\Common7\IDE\devenv.exe)(HOST=ELROND)(USER=webadmin))))
I looked up the error and I believe it has to do with the parentheses in the
"Program Files (x86)" argument. Apparently Oracle considers the parentheses
special characters and will fail to connect if it sees them. I don't know
how to fix it though! I uninstalled the SSIS client tools and tried to
reinstall them in a different folder but the install doesn't allow you to
change the location of the Business Intelligence Studio. Any suggestions?
The second issue is more clarification than an actual problem. Our server
is 64-bit but our development boxes are 32-bit (WinXP SP2). Will we have any
problems developing our SSIS packages on a 32-bit environment and then
deploying to 64-bit?
Any and all help is appreciated!
Mike

JESC

2006-02-01 22:38:02 UTC

Permalink

I am having simular problems getting SQL Server 2005 64 bit to talk to
Oracle. Did you ever find out what the issue was?

Mike L

2006-02-01 22:59:38 UTC

Permalink

Hi JESC,

I submitted the issue to Microsoft support and they helped me discover a
work-around. The issue is with the oracle provider and the (x86) directory
used by the developer environment and the management studio.

The solution is to develop and test your package on a 32 bit machine, DEPLOY
it to the 64 bit server and then run it as a SQL Server Agent JOB. Should
work fine. The only catch is I think the database provider for 32 bit and 64
bit machines have to be the same version. I'm using the .Net provider for
Oracle since Microsoft doesn't provide a 64 bit version of their OLE DB
provider. I think you can also use the OLE DB provider that comes with the
Oracle 10g client tools (It's an extra option so you have to do a custom
install, not just the runtime). They don't make a 64-bit 9i version of the
client tools.

Hope this helps!
Mike

Post by JESC
I am having simular problems getting SQL Server 2005 64 bit to talk to
Oracle. Did you ever find out what the issue was?