Connecting to Oracle DB using SSIS

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

Why doesn't it "seem to work"? What errors are you getting? What isn't happening? What IS happening?

Have you got Oracle client installed? Have you configured OLE DB Driver with the name of the TNS entry?

-Jamie

|||

This is the error msg I get.

Test Connection failed because of an error in initializing provider. ORA-12514: TNS listener does not currently know of service requested in connect descriptor.

I have Oracle client installed.

How do I configure OLE DB Driver with the name of the TNS entry? I am new to Oracle...so please bear with me..

Thanks a lot..

|||

First of all you need to verify that you can connect to oracle using the configured driver before you try to do it in SSIS.

There is a file on your client called tnsnames.ora. That file should contain what is called a TNS entry that contains all teh connection details for your Oracle server. Your Oracle DBA will give you the TNS entry.

Verify that you can connect and then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI.

-Jamie

|||

Hi Jamie,

Thanks for a quick reply. I have an entry in the tnsnames.ora file for the connection details. So I checked to see if I can connect with the host string in the tnsnames.ora along with the user name/password. To do that I used Oracle SQL* PLUS. I am able to connect.

I couldn't understand the second step...

"then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI." Can you please explain in more detail?

Thanks a lot...I really appreciate your help.

|||

When you build the SSIS Connection Manager using the microsoft OLE DB Provider for Oracle you will see a box labelled Server Name. Copy the name of the tns entry into that box.

-Jamie

|||

I got it Jamie. I was having two locations for tnsnames.ora and that was the reason for the error...

I appreciate your help.

Thanks.

|||

Hi Jamie,

I have the same problem now in production environment. I have to connect to oracle db from production too and I downloaded the client tools on my 64 bit windows server 2003. I edited the tnsnames to have an entry. When I try to connect to oracle, it gives me an error saying client and networking tools not installed, but I did install them. Can you please help me?

Thanks.

Sam.