This content has been marked as final. Show 4 replies
We are running into a very similiar issue. We have a 2005 SQL Server system in which the link server runs fine with the Oracle Provider for OLE DB, but the Legacy DTS's fail with the same type of error, i.e., "error initializing provider". We believe it's related to a 32bit / 64 bit issue where the 64bit MS software is not able to see the Oracle provider. Have you determined the exact cause and found a fix yet?
what we did was to install both oracle clients one 64 and 32 on the server with sql server 2005 and now i can use link server as well as creating packages and datasources on SSIS
mtaji wa maskini,
Cheers for the day.
We came across your post of successful installlation of Oracle 32 and 64 bit versions ,for connecting Oracle from BIDS 2008.
We are facing similar problem in connecting.We have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2008 (x64 bit) installed on it.
Now we want to connect to a Oracle database,for that we installed Oracle 11 g client full (32 bit and 64 bit) versions.Also changed the settings in the tnsnames.ora file by giving the host name of Oracle server.But there is still porblem in connecting to the oracle database server.
"oraoledb.oracle.1 provider is not registered"
Can we please guide us through the process of installation.We highly appreciate your time and efforts.
Hopes this helps others, I spent almost the whole day trying to setup Win 2008 64bit server using SQL 2008 and SSIS. In addition to installing the Oracle 11g 64bit and configuring the environment variables and the tnsnames.ora, I still had access problems within SSIS connecting to Oracle tables. Looking over another working server which was 32bit, I found that I installed ODAC with ODT for Visual Studio on it some time ago, probably to fix this problem.
At the Oracle page: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
Download: Oracle 11g Release 2 ODAC and Oracle Developer Tools for Visual Studio 184.108.40.206.2
Then install it on the server. You can test it by starting SSIS on the server, that is if it is not production, to test successful connection. It instantly resolved the SSIS problem for me.