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

Testconnectionfailedbecauseofanerrorininitializingprovider.Oracleclientandnetworkingcomponentswerenotfound.ThesecomponentsaresuppliedbyOracleCorporationandarepartoftheOracleVersion7.3.3orlaterclientsoftwareinstallation.
Providerisunabletofunctionuntilthesecomponentsareinstalled.

问题的原因：

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

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

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

解决办法如下：

  • DownloadtheOracle11Gfullclient.
  • Installthe32ANDthe64bitversionofthe11Gfullclient(InstallationType:Administrator)andreboottheserverafterwards.The32bitversionisneededfordevelopmentfromBIDSwithis32bit,the64bitversionisneededforproductionwiththeSQLAgent,whichis64bit.
  • ConfiguretheOracleclients(both32and64bits)byeditingthefilestnsnames.oraandsqlnet.ora.TrytodothiswithanOracleDBAor,evenbetter,lethim/herdothis.
  • Usethe"OracleproviderforOLEDB"fromSSIS,don'tusethe"MicrosoftProviderforOracle"becausea64bitversionofitdoesnotexist.
  • ScheduleyourpackageswiththeSQLAgent.


对于SQLServer2008R2的机器需要做额外配置：

1.OpenREGEDIT(Start…Run…REGEDIT)ontheserverandsearchforthefollowingentry(forthe32bitsdriver):HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI
Makesurethefollowingvaluesareentered:

p_w_picpath

2.Next,searchfor(forthe64bitsdriver):HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI
Makesurethesamevaluesasaboveareentered.

3.Rebootyourserver.

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