CODEWITHSUNDEEP
oracle-databasessisoracle11g
user1603734
user1603734

Reputation: 1131

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

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?

Upvotes: 6

Views: 18576

Answers (4)

Ali Emre Can
Ali Emre Can

Reputation: 33

You need to type "Servername/ServiceName" to Server Name Field .

Upvotes: 0

rtev
rtev

Reputation: 1122

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.

Oracle SQL Developer Connection Properties

Visual Studio Connection Properties

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

Upvotes: 11

dev_huesca
dev_huesca

Reputation: 506

Ok ok, been here, and this is what works:

You need oracle data access components http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Download the one that applies to you (11g, 12c...) It installs an OLE DB provider which is FAST and also .NET connectors (wich also work really well), so I recommend using OLEDB (also, attunity connector works wonders aswell after you installed ODAC).

Then just connect using ip/service name + credentials, for example

Image

Upvotes: 0

Dominic Goulet
Dominic Goulet

Reputation: 8113

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!

Upvotes: 2

Related Questions