How do I connect to Oracle with SSIS? I am getting the error: "tns listener was not given the service_name in connect_data"

Question

I am trying to connect to an Oracle DB through the Oracle Provider for OLE DB in SSIS, but am getting the following error when testing the connection: "tns listener was not given the service_name in connect_data". I am not too familiar with Oracle, so could someone provide me some direction for how to fix this error?

Answer 1

You can add the service name after the host name with a forward slash between them. This has worked in both the Microsoft and Oracle OLE DB providers.

IP/Service_Name also works, "12.12.123.123/Server1.domain.com"

Answer 2

First of all, always use Oracle driver. Not the Microsoft one.

Then, when connecting, supply the TNSNAME, USERNAME and PASSWORD to a OLEDB Connection.

That's all!