SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OLE DB data provider for oracle 11G in SSIS


OLE DB data provider for oracle 11G in SSIS

Author
Message
abbi1680
abbi1680
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 66
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
Gift Peddie
Gift Peddie
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6148 Visits: 14456
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search