ssis oracle配置,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\MTxOCIMakesurethesamevaluesasaboveareentered.

3.Rebootyourserver.