64位機器上SSIS 連接配接Oracle報錯

早上有個朋友發郵件給遇到一個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:

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

轉載于:https://blog.51cto.com/lzf328/1300938