Create a connection to Oracle 10g on 64bit windows

[xtreemnet]

Hi,

Can someone tell me the steps to create a connection to oracle 10g on a server which runs on Windows 2003 server(64bit)?

I need to update a SQL 2005 table from data on Oracle 10g.

Thanks.

 

[Viass]

To connect to Oracle, first you need create a connection using the 'Microsoft OLE DB Provider for Oracle'. You will then be asked the Oracle Servername, Username and Password.

Once the connection is established, you will be able to access the Oracle tables using the 'OLE DB Source'Task.

Hope this helps.

 

[paulkeys]

Word of warning on this one... (You'll like this!!)

The exe that fires the packages and also the exe for the BIDS reside under the 64 bit program files folder. This folder has a bracket in the file path.

Brackets are reserved words in oracle, so lots of interesting errors arise when trying to establish a connection to oracle through BIDS and when running a package.

The workaround we implemented for this was;
1. When using BIDS run it from the command line using the dos friendly prompt - e.g. C:\PROGRA~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe. This removes the bracket from the exe and thus the connection succeeds.
2. When running a package, run from command line using the DTEXEC syntax - i believe this fires from the 32 bit program files folder, so no bracket issue!!!!

 

[xtreemnet]

Hi,

I can now execute SQL task which uses oracle 10g connection. But i have not been able to connect to an oldeb source component using the oracle connection manager.

Any help is appreciated.

Thanks

 

[Viass]

'But i have not been able to connect to an oldeb source component using the oracle connection manager'..

Have you got any error message?

 

[xtreemnet]

This is getting very funnuy. When I test my connection manager it fails. But my Execute SQL task which uses this connsction works fine.
I have created another similar connection manager for Oracle and try to assign it to an oledb component, and it errors out saying 'Cannot acquire connection from connection manager..'