SSIS 连接Oracle报错 These components are supplied by Oracle

早上有个朋友发邮件给遇到一个SSIS连接Oracle的问题，具体的错误信息如下： Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Cor

早上有个朋友发邮件给遇到一个SSIS连接Oracle的问题，具体的错误信息如下：

 

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.        

 

问题的原因：

 

1.因为BIDS是32位的应用程序即使在64位的机器上面，所以无法使用64位的Microsoft Provider for Oracle.所以从64位的机器上连接Oracle无法成功。

 

2.因为Microsoft Provider for Oracle只提供了32位的版本，而且微软没有计划在将来发布64的版本。

 

3.Oracle客户端自身问题，需要安装完整11G客户端。

解决办法如下：

• Download the Oracle 11G full client.

• Install the 32 AND the 64 bit version of the 11G full client (Installation Type: Administrator) and reboot the server afterwards. The 32 bit version is needed for development from BIDS with is 32 bit, the 64 bit version is needed for production with the SQLAgent, which is 64 bit.

• Configure the Oracle clients (both 32 and 64 bits) by editing  the files tnsnames.ora and sqlnet.ora. Try to do this with an Oracle DBA or, even better, let him/her do this.

• Use the "Oracle provider for OLE DB" from SSIS, don't use the "Microsoft Provider for Oracle" because a 64 bit version of it does not exist.

• Schedule your packages with the SQLAgent.

 

对于SQL Server 2008 R2的机器需要做额外配置：

 

1. Open REGEDIT (Start… Run… REGEDIT) on the server and search for the following entry (for the 32 bits driver): HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI      
Make sure the following values are entered:

2. Next, search for (for the 64 bits driver): HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI       
Make sure the same values as above are entered.

3. Reboot your server.

 

来源于：http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx