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.1’ provider is not registered on the local machine.
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”.
How to fix this:
- Software needed https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
- 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:
- 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.
