Author |
Topic |
Pherkad
Starting Member
23 Posts |
Posted - 2007-04-30 : 09:50:23
|
hi all.In my SSIS project, i have to retrieve data from an oracle 10g database. On my personal laptop it works, but on our test-env i get the error below, when i try to test my connection to the oracle datasource.Could this because i have oracle XE installed yet on my personal laptop, and it is not installed on the computer of our test environment where the SSIS runs? If that's the reason, it sounds very strange, because it would mean i have to install an oracle environment also on the computer with SSIS, just to make a connection to another oracle server...Error:TITLE: Connection Manager------------------------------Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.Provider is unable to function until these components are installed.------------------------------BUTTONS:OK------------------------------ |
|
Pherkad
Starting Member
23 Posts |
Posted - 2007-04-30 : 10:12:52
|
is it maybe the oracle instant client that i need to install on the SQL Server PC ?http://www.oracle.com/technology/software/tech/oci/instantclient/index.html |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-04-30 : 10:20:58
|
you need to install the Oracle client first. INstant client will probably work, but I have not used it with SSIS or even with DTS. make sure you also get a copy of your tnsnames.ora so you can resolve the names of your oracle instances.-ec |
|
|
Pherkad
Starting Member
23 Posts |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
Pherkad
Starting Member
23 Posts |
Posted - 2007-04-30 : 10:53:20
|
thanks !!!!but...euhmmm....400Mb for the client?will it be such a heavy program / installation?just for allowing my server to contact a pc where an oracle runs????how comes it's such a huge download? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-04-30 : 10:55:58
|
quote: Originally posted by Pherkad thanks !!!!but...euhmmm....400Mb for the client?will it be such a heavy program / installation?just for allowing my server to contact a pc where an oracle runs????how comes it's such a huge download?
it just is try the instant client then. -ec |
|
|
Pherkad
Starting Member
23 Posts |
Posted - 2007-05-02 : 04:43:50
|
now i've installed the oracle XE on a pc, called EVO610.on my pc with SSIS I installed the Oracle 10g runtime client as mentioned above.when i now try to add a Datasource (with as provider: .Net Providers\OracleClient Data Provider), I get the error below...(for servername, i fill in EVO610 - thus the machinename, and as user and pwd I fill in SYS and the pwd i created.error:Test connection failed because of an error in initializing provider. ORA-12514: TNS:listener does not currently know of service requested in connect descriptorwhat can this be? |
|
|
Pherkad
Starting Member
23 Posts |
Posted - 2007-05-02 : 08:19:03
|
damn what a mess. now it finally works.with the instant client alone, it doesn't work (no tnsnames.ora,... was created).1)i installed first the instant client, thereafter the runtime (both are in the big installation file above)2)set system variables TNS_ADMIN and ORACLE_HOME manually (unbelievable that you have to do it manually)%ORACLE_HOME%=C:\oracle\product\10.2.0\client_2%TNS_ADMIN%=C:\oracle\product\10.2.0\client_2\NETWORK\ADMIN3)insert in the tnsnames.ora file (of the client pc): EVO610 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.1.114)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = XE) ) )#the service_name is what is configured under DEFAULT_SERVICE_LISTENER on the server pc, in the file C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora(if tnsnames are well configured and able to connect to the server, you can check in advance with cmd>tnsping xxx)4)client pc:> odbc client administrator> add new system dsn> after clicking add, you will find a newly added 'instantclient' driver. there you can connect with the TNS name above.5)now you can add a new data sourece in SSIS:provider: native oleDB / native ole db provider for oracleif someone has remarks or questions, don't hesitate. it was a hell to find this all out myself, under extreme time-pressure, in a jungle of ducumentation and with few Oracle Administration knowledge.Also thanks to eyechart for giving me the link to download the full 10g Client.... I don't understand why it's not on the main download page of 10g (the link in my post above)... I still wonder how you found that page. |
|
|
Pherkad
Starting Member
23 Posts |
Posted - 2007-05-02 : 08:39:24
|
quote: I don't understand why it's not on the main download page of 10g (the link in my post above)... I still wonder how you found that page.
OK, i found that you just pointed me to the enterprise/standard edition, which included a separate client runtime installation... (it's very confusing that docs say: download the client, while you have to know that instead of downloading the client, you have to download the full installation... and within the full installation, only install the runtime client (and/or instant client) component. |
|
|
SRA
Starting Member
1 Post |
Posted - 2010-11-23 : 14:38:46
|
Hi - The Instant Client allowed Windows to add a Microsoft ODBC for Oracle driver. So thanks for that info.When I tried to create an Oracle Data Source Connection, I get an error that says:"This Oracle DSN has been configure wiht an unsupported ODBC driver (version 2.575.1132.0 (xpsp.080413-0852)) Please update the ODBC driver to version 9.2.8.0 and above."The InstantClient was version 11.2.0.1.0.Anyone know what this is all about?Thanks |
|
|
|