We are migrating the 2000 server to 2005 Enterprise Manager 64bit (running under windows 2003 64bit). The first instance was no problem and it is working fine. All the dts from SQL 2000 are working perfect. We are still using the DTS because it works in production. In the near future we will rebuild them in SSIS. The second instance is the problem. Databases, DTS to legacy, jobs, security and so on no problem. The problem is that in the dts we use ODBC connection to the source database Oracle 9... No we are facing huge problems: we can't get the ODBC working from DTS, SSIS to the oracle database. Does anyone has the solution for working from 2005 to oracle databases with dts. I have read a lot of solutions as install 10g clients, install not in (x86) path, ORACLE_HOME path, and so on.
I hope someone can give me the right solution for this problem.
You generally face two problems using Oracle with SQL Server x64. The first is that the tools are 32bit, so to manually run a package for development or testing purposes, you have to have the 32 bit Oracle client installed as well as the 64bit Oracle client installed. When the packages actually run using DTEXEC, they do it with the 64bit driver if the 64bit version of dtexec is used. The second problem which you have already found is that the Oracle client takes a dump if you call it from a path that has a parenthesis in it (ie (x86)). The solution is to install the client outside of that path, but I also had to copy the Microsoft SQL Server (x86) path to Microsoft SQL Server x86 and then call the DTEXEC executable from there to get it to work.
answered Dec 14, 2009 at 03:40 PM
I have it all working now without (86) parenthesis problem which I never faced. What have I done:
Download software InstantClient 10.2.0.4 voor 32bit InstantClient 10.2.0.4 voor 64bit
Unzipped in d:\instantclient_10_2_32 and d:\instantclient_10_2_64
Changed the enviroment variable (windows) adding to PATH variabele d:\instantclient_10_2_32;d:\instantclient_10_2_64
Added an new variable TNS_ADMIN with the value where the tnsnames.ora is located (in our case c:\beheer)
Install drivers from unzipped folders (command line to see what happens). First the 32bit and then the 64bit (ODBC_INSTALL.EXE)
Check regedit for the ODBCINST.INI (SOFTWARE\ODBC and SOFTWARE\SYSWOW64\ODBC) if the path d:..... is installed
Most important check if the MFC71.dll and MSVCR71.dll are installed in the SYSWOW64 directory. If not download or copy them from another machine. We where missing the MFC71.dll
Reboot the server (standard if we change anything and to be sure that nothing is left behind in the registry, memory and do on)
Create an ODBC driver using the controle panel\administrative tools\ODBC manager (this one is the 64bit ODBC administrator) and create an ODBC driver using the run : c:\windows\syswow64\odbcad32.exe (this is the 32bit administrator)
I have tested it with DTS, SSIS and it runs perfect. Also the DTS are running with DTSRun from the schedular.
Thanks for your replies.
answered Dec 15, 2009 at 07:49 AM