July 25, 2011 at 8:09 pm
Hi Guys,
We are migrating from SQL SERVER 2005 to SQL SERVER 2008R2. SQL SERVER 2005 is on one server and SQL SERVER 2008R2 is totally on different server.
SQL SERVER 2005 is installed on Windows 2003 Server
SQL SERVER 2008R2 is installed on Windows 2008R2 Server.
I am facing problems with the SSIS packages after migrating from 2005 to 2008R2. In SQL SERVER 2005 they are running perfectly fine. But when executed in SQL SERVER 2008R2, the SSIS package is throwing the following error :
--------------------------------------------------------------------------------------------------------
Error at EMPLOYEE_PKG [Connection manager "Source Microsoft"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.
Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "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.".
Error at Process Venue [ EMPLOYEE Venue [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Microsoft" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
----------------------------------------------------------------------------------------------------
I am getting this error when I try to look into the source of the package from BIDS. When googled regarding this error, came know that Oracle should also be installed on the new server.
So got the Oracle 11.2.0 (x64) installed on the new Server where SQL SERVER 2008 R2 is installed. And rebooted the server also. But still no luck, I am still getting the same error and when clicked on the source in the connection manager in the BIDS, it is saying that it cannot find the Oracle Provider.
It also gives the following error mesage :
------------------------------
Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.
------------------------------
Can anyone provide suggestions of how to resolve this issue???
Thanks,
July 25, 2011 at 8:18 pm
You need to install the Oracle Client on the new Server.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2011 at 8:52 pm
I already got the Oracle 11.2.0 (x64) installed on the new Server where SQL SERVER 2008 R2 is installed. And rebooted the server also. But still no luck, I am still getting the same error
July 25, 2011 at 9:08 pm
grkanth81 (7/25/2011)
Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "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.".
Hmm, every time I have seen this error there was an issue with the Oracle Client Installation.
Where did you execute the package from and where did you install the Oracle Client?
Are you running the package from BIDS or the SQL Server Agent?
If you are still getting the "same" error you definitely have an issue with the Oracle Client not being installed on the machine that you are running SSIS from.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2011 at 10:13 pm
I am executing my SSIS package through BIDS and I am executing it on the server only.
I have again checked and I can see that Oracle is installed on my server.
I have googled about this error but couldnt get an exact solution...........
July 25, 2011 at 10:32 pm
Are you logging on the server or are you executing the package from you local machine?
Make sure that you have the client on your local machine and on the SQL Server Box.
I'm certain that you are missing an Oracle Client installation on the machine that you are executing the SSIS Package from.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 27, 2011 at 6:21 pm
Thanks for u r response....
After lots of research, I finally figured out how to resolve this issue.
I just followed the below website and installed Oracle 32 bit version also and then that got fixed.
Here is the website with clear instructions............
July 27, 2011 at 9:52 pm
Glad that you figured it out.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply