Solved

SSIS 2012 OLE DB connection to Oracle results in ORA-12154 error

Posted on 2014-04-07
6
8,673 Views
1 Endorsement
Last Modified: 2016-02-10
Hi All

I'm running SSIS 2012, and am trying to make a connection to Oracle, but it is returning the below ORA-12154 error.  Need help resolving this.

ssis-oracle-connection-errmsg-af.jpgThe same box is running DbVisualizer, which is able to successfully connect to Oracle using the Oracle Thin JDBC driver.

dbvisualizer-oracle-thin-connect.jpgI tried downloading and installing the Microsoft Connectors v2.0 for Oracle and Teradata, which installed successfully, but generates the same error message.
 
Thanks in advance.
Jim
1
Comment
Question by:Jim Horn
  • 3
  • 3
6 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39984351
Did you download and install the Oracle Client?  The Microsoft drivers sit on top of the Oracle client.

When you have the Oracle client installed you need a tnsnames.ora file in the network/admin folder with an entry that has the database information.

The alias you give it is what goes in the service name field.

The reason the secon one works is that the JDBC Thin connection doesn't need an Oracle client.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 0 total points
ID: 39985829
>Did you download and install the Oracle Client?
Sorry, forgot to add the 'SQL expert, Oracle N00b' disclaimer in my original question, so the answer would be 'How would I know if Oracle client is installed?

>When you have the Oracle client installed you need a tnsnames.ora file in the network/admin folder with an entry that has the database information.  
Below is the search results for tnsnames.ora.  Looks like there are muiltiple files in a ...\network folder.  
 Windows Explorer search results for tnsnames.oraThe top one have a date modified of yesterday, around the time I was installing 12c, but when I open it in UltraEdit it is empty.

The second one has the same date modified, and when I open it in UltraEdit it displays the below image.

The bottom one has a date modified of 12/13/2007, is there, and when I open it in UltraEdit it also displays the below image:tnsnames.ora>The alias you give it is what goes in the service name field.
Don't know what 'alias' and 'service name field' means, although in the tnsnames.ora files it is showing SERVICE_NAME=ORCL, which I'm guessing was a default setting I chose during the install.

>The reason the secon one works is that the JDBC Thin connection doesn't need an Oracle client.
(Potentially stupid question) Would SSIS have a way of accessing the JDBC Thin Connection?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39986842
>> 'How would I know if Oracle client is installed?

If it isn't the Instant Client, there should be an Oracle folder in c:\program files.  This is the location of the inventory file.  The client itself is typically installed in some other folder specified at install time.  The inventory file or registry should provide the location(s).

The one I would recognize as the actual install is the one under ...app...client_1...\network\admin

The network/admin folder is the 'default' location Oracle looks in.  It can be overridden with a LOCAL environment variable on Windows (TNS_ADMIN on Unix).

The other tnsnames.ora files (trax) look to be for some other product(s) that might come with their own Oracle client.

>>Don't know what 'alias' and 'service name field' means

In the tnsnames file you posted, jim.horn is the alias.  If is set to look on the HOST for a listener on PORT and will try to connect to the Oracle instance specified in SERVICE_NAME


>>Would SSIS have a way of accessing the JDBC Thin Connection?

Don't know.  Oracle person here.  Don't know crap about SQL Server.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 0 total points
ID: 39986886
>If it isn't the Instant Client, there should be an Oracle folder in c:\program files.
There is no c:\Program Files\Oracle\ folder on this box.  This may explain why this place (I started a new contract gig two weeks ago) only connects to this Oracle db using DbVisualizer, using the JDBC Thin Client, instead of using a DSN, or any Oracle-specific UI such as SQL Server SSMS.

Wonder if an Oracle Client is required for SSIS to successfully connect.

Regarding the alias / host / service_name, that's either moot per the above, or bigger than a single EE question, so I'll chew on verifying if the above statements are correct, and then close the question, unless something better rolls around.

Thanks.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39986922
>>There is no c:\Program Files\Oracle\ folder on this box

The image you posted shows an install in Program Files (x86).  Look there.

>>Regarding the alias / host / service_name, ...

I'm fine with this as a single question since it is all sort of meshed together.

>>Wonder if an Oracle Client is required for SSIS to successfully connect.

Don't know.  I know that the JDBC Thin and new .Net Managed Driver are the only two ways to connect to an Oracle database w/o an Oracle client.

There are some 3rd party apps like DevArt that will allow a clientless connection but a product install is a product install?

The Oracle Instant Client is the smallest footprint for an actual client install:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

It does require some manual things like manually creating the network/admin folder and placing a good tnsnames.ora and sqlnet.ora file in it.  The full client has tools to help you configure these files and to aid in 'testing' like tnsping.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 0 total points
ID: 40017519
I was able to successfully connect to Oracle.  
Install BOTH the 32-bit and 64-bit Oracle by Attunity drivers.
Edit the tnsnames.ora file to contain your connections, in a format that looks like this
connection_1 =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_server)(PORT = the_port_usually_1521))
    (CONNECT_DATA =
      (SERVICE_NAME = your_database)
    )
  )
connection_2 =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = your_database)
    )
  )

Open in new window

Toolbox with Oracle connectionsAdd SSIS Connection ManagerOracle Source
Stuff I also did, but not abundantly certain if it was necessary..

Environment Variables
Start button, Control Panel, in the search textbox type ‘Environment’
Click on ‘Edit the system environment variables’.
In the System Properties box that appears, Advanced tab, click on the Environment Variables button.

Add these System Variable (substitute your name for mine),
Name      Value
ORACLE_HOME       C:\app\jim.horn\product\12.1.0\client_1
TNS_ADMIN               C:\app\jim.horn\product\12.1.0\client_1\Network\Admin
Path      Contains many paths separated by semicolons.  Make sure the first one, that is Oracle-specific, reads ¿ C:\app\jim.horn\product\12.1.0\client_1

Other Stuff I installed and uninstalled
The following is a list of other things I’ve done at one point to pull this off, just in case it is required.
32-bit Oracle Data Access Components
Oracle 12c Data Provider for .NET

Note to everyone reading this post:  I'm guessing this script will be outmoded by the end of 2014, as changes to any of these applications would result in a change to the process.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now