早上有个朋友发邮件给遇到一个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:

64位机器上SSIS 连接Oracle报错_SSIS ORACLE 64 bit

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