Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-07
6
Medium Priority
?
10,047 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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 66

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 66

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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 66

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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