Things to remember when you working in 64bit environment
Potential problems in a 64-bit setup
32-bit tools not installed:
- There is no 64 bit Jet driver, so that means no Excel or Access support on 64bit OS.
You may get the following error. To resolve this issue run SSIS package using 32bit version of DTSExec
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered
- There is no JIT for VSA scripts on 64 bit, so scripts must be compiled when developing the package (through the 32-bit designer tools). The
PreCompile should be set to true, which is the default. The script is then compiled when the package is saved.
- You cannot remotely debug on IA64.
There is no Visual Studio support for Itanium (IA64) and therefore no BIDS, so packages need to be developed on another machine, but can then be deployed to and executed on IA64 machines. There is however full support for BIDS on x64 platforms.
- When designing packages on a 64-bit machine, the default behavior is to run in 64-bit mode. You may want to change this so that you can access 32-bit only resources, e.g. Jet. Within the Project Properties, select the Debugging page, and change the
Run64BitRuntime property to false.
Run64BitRuntime property for integration services project
- The default t DTExec used will be the 64-bit version, but if you have elected to install the 32-bit runtime as well, you can use the 32-bit version of DTExec. This allows you to access 32-bit only resources such as Jet again. See
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe
- On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTExec should be used. As above the 32-bit version can be found in
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.
- Executing packages through SQL Server Management Studio (SSMS) on x64 or I64 machines will use a 32-bit execution host. So executing packages from within the object explorer may not be ideal.
- DTExecUI.exe, the nice GUI sibling of DTExec is 32-bit only. To ensure you run as 64-bit just use DTExec.exe.
The 32-bit runtime and tools are not automatically included when you install 64-bit Integration Services. If you have packages that need to run in 32-bit mode, then you have to select an additional option on the Feature Selection page of Setup. On x64, this can be BIDS or Management Tools - Complete; since BIDS is not available on Itanium, on Itanium you must check Management Tools - Complete.
32-bit tools run by default: When the 32-bit tools are also installed, the path to the 32-bit tools appears BEFORE the path to the 64-bit tools in the PATH environment variable. Therefore, if you just type "dtexec" at the command prompt, for example, you are running the 32-bit version of the tool. Your options are to type the full 64-bit path, make the 64-bit directory the current directory first, or change the order in the PATH environment variable. Our testing has not shown any negative side-effects to changing the order of the PATH, but I make this suggestion cautiously.
Features that DON'T work on any 64-bit operating system (x64 or Itanium)
You cannot run DTS packages in 64-bit mode on x64, or at all on Itanium. Therefore you also cannot use the Execute DTS 2000 Package task in these circumstances. Of course on x64 you can run DTS packages, or SSIS
packages that run DTS packages, in 32-bit mode, after manually installing the optional DTS run-time support.
Excel, Access, Jet: You have to run packages that use the Jet provider (Access and Excel) in 32-bit mode.
SQL Server Compact: You have to run packages that use the SQL Server Compact provider in 32-bit mode.
Logging to SQL Server Profiler: You can only use package logging to SQL Server Profiler in 32-bit mode.
Copyright © 2018 SQL Sentry, LLC All Rights Reserved