Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OLE DB data provider for oracle 11G in SSIS Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 17, 2013 4:03 PM
Points: 3, Visits: 61
I would need a favour from you.
I have installed oracle 11G and SQL server 2008 in my laptop.
I have run some packages in SSIS and they are all working fine.

And I had a problem in connecting to Oracle 11G through SSIS.
SSIS is not able to find out OLE DB data provider for oracle 11G.

This is the error I encountered and I used ORACLE PROVIDER for OLE DB to connect to the oracle 11G.

I have got the client and able to connect through Oracle Developer.

Error :

Test Connection faied because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specifed.

I spent a couple of days to sort this out but could not succeed.

Could you please suggest me if you are aware of this problem.

I then checked the listener status to see if it works ok. It seems everything is fine.
Please find the below.


C:\Documents and Settings\seshireddy>lsnrctl status


LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 20-MAY-2009 12:55
:15

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date 20-MAY-2009 12:47:14
Uptime 0 days 0 hr. 8 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\seshireddy\product\11.1.0\db_1etwork\admin\li
stener.ora
Listener Log File c:\app\seshireddy\diag\tnslsnr\divya\listener\alert\lo
g.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=divya)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Documents and Settings\seshireddy>


Thanks
Seshi
Post #720496
Posted Monday, May 25, 2009 9:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
TNS Names not resolved means you have permission problem, you said Oracle 11g is installed locally then you need to go to BIDS and configure your connection to Oracle. I also think you need to put you TNSNAMES.ORA file in your development folder, so manually configure Oracle connection in BIDS and put your TNSNAMES.ORA file in your development folder.



Kind regards,
Gift Peddie
Post #723003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse