Test connection failed because of an error in initializing provider. Oracle client and networking components were not found

You may experience the following error. When a developer installed oracle client and tested the visual studio BIDS packages ask you to fix this. We know the MS only has a visual studio BIDS 32 bit.

Recently, It asked in the forum, I had managed Oracle sometimes and had same kinda issue couple more times–> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b6efab4-c26f-45cd-aca6-4ba3d53f13a4/the-oraoledboracle1-provider-is-not-registered-on-the-local-machine?forum=sqlintegrationservices

How can we find which bit of oracle installed on:

How to Tell if Oracle Client is 32 or 64 bit installed on Windows

Errors:

1) 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.

2) TITLE: SQL Server Import and Export Wizard The operation could not be completed.

The ‘OraOLEDB.Oracle’ provider is not registered on the local machine. (System.Data)

When you do a ETL from SQL server to oracle database, you might get this error, especially on 64 bit windows.

3) The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available.  Error code: 0x00000000.

An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.

Cannot create an instance of OLD DB provider “OraOLEDB.Oracle” for linked server server. (Microsoft SQL Server, Error 7302)
The OLE DB provider “Oracle” has not been registered. (MicrosoftSQL Server, Error 7403)

You can also use EZConnect identifier, Instead of a TNSNames alias.

https://docs.microsoft.com/en-gb/archive/blogs/dbrowne/creating-a-linked-server-for-oracle-in-64bit-sql-server

How to fix this:

1. Software needed https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
2. Steps to troubleshoot

Download the both Oracle 32 and 64 bit 11G or latest S/W – full client do a full Administrator installation, you can also select whichever you want, if you know it.

Configure the “tnsnames.ora” to connect the oracle database, you can do this by NETCA using same installation or easy one get the file from oracle server and copy paste it to the right location OH Admin folder, you can get a oracle DBA help (OR) You can do it yourself go to RUN–> regedit –> HKEY_LOCAL_MACHINE –> SOFTWARE –> ORACLE or if you have already tnsping DBname or echo $ORACLE_HOME. Find the oracle home and paste it.

$ORACLE_HOME\Network\Admin\ directory”

Ex: F:\Oracel_32Bit\product\11.2.0\client_1\Network\Admin\Tnsnames.ora”.

Sample Tnsnames.ora

DB_test =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(Host = DBname.abcd.com)

(Port = 1521)

)

(CONNECT_DATA =

(SID = DBname)

)

)

Set the environment variable by Right click My computer –> Properties –> Advanced system settings –> Click –>Advanced tab–>Environment Variables –>Click –> Navigate Path –>Click edit –> paste the 32 bit client installation first and “;” 64 bit installation path and leave the remaining.

Ex: F:\Oracel_32Bit\product\11.2.0\client_1\bin;F:\Oracle_64Bit\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows and rest you can leave it. Oracle_Home and Admin if you want you can set as well.

Reboot the server.

Testing steps:

1. Try to connect oracle database to make sure you have a connectivity.

In command line:

= = = = = = = = = =

C:\Users\username>tnsping DBname — Type this.

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 04-NOV-2

017 08:59:01

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

F:\Oracel_32Bit\product\11.2.0\client_1\Network\Admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = sukgch0

5) (Port = 1521)) (CONNECT_DATA = (SID = DBname)))

OK (50 msec)

Even you can try connect the oracle user and password by CMD. If you have an account given by oracle team. Type the following and enter the password.

= = = = = = = = = =

U:\>sqlplus username@dbname

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 4 15:28:35 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 – Production

SQL> select * from user_tables;

no rows selected

 

You will get the response like the above, if it does not ,you have a problem with database connection you need to fix it before move to next step.

Once you have a connection with oracle database, test the provider connection by import and export wizard.

Testing BIDS/ SSIS package / OLEDB provider:

Connect SSMS –> Go to database –> Right click –> Tasks–>Import data –>Source Microsoft OLED provider for oracle (OR) Oracle provider for OLE DB –> enter the server and credential detail and test it.

If you have SQL agent job check the package run mode 32 or 64 bit in the agent job step and test it and make a change.

Fell free to drop me an comment, I have installed Oracle on my VM, I can help it.