SSIS: 64-bit Story
With the introduction of 64-bit processors, there seem to evolve two main flavors of this new processor architecture: x86-64 and IA64.
First, let's take a look at different flavors of 64-bit, namely x86-64 and IA-64. And then, I'll explain what the WOW64 feature is, and how Windows applications, including SSIS, can take advantage of this Windows feature. What components of SSIS are compiled in what architectures, and how to use them in 64-bit would then be explained.
64-bit Flavors: x86-64 and IA-64
In computing, IA-64 (short for Intel Architecture-64) is a 64-bit processor architecture developed cooperatively by Intel Corporation and Hewlett-Packard (HP), and implemented in the Itanium and Itanium 2 processors. The goal of IA-64 was to produce a "post-RISC era" architecture that would address some of the key challenges faced by older architectures, to enable more efficient performance scaling in future processor designs.
AMD's AMD64 architecture, first delivered in 2003, found success in the marketplace, the Itanium was delayed, and Intel developed and announced the EMT64 architecture, which is very similar to AMD64. Both architectures are 64-bit upgrades to the x86 architecture, and the two are often referred to generically as the "x86-64 architecture". In 2006, Intel delivered x86-64 processors based on the Intel Core microarchitecture.
What is WOW64?
Windows XP x64 Edition uses a technology named WOW64, which permits the execution of 32-bit x86 applications. It was first employed in Windows XP 64-bit Edition (for the IA-64 (Itanium)), but then reused for the “x64 Editions” of Windows XP and Windows Server 2003.
Since the X86-64 architecture includes hardware-level support for 32-bit instructions, WOW64 simply switches the process between 32- and 64-bit modes. As a result, X86-64 architecture microprocessors suffer no performance loss when executing 32-bit Windows applications. On the IA-64 architecture, WOW64 was required to translate 32-bit x86 instructions into their 64-bit IA-64 equivalents—which in some cases were implemented in quite different ways—so that the processor could execute them.
How to develop and deploy SSIS applications on 64-bit machines?
Although 32-bit applications can be run transparently, the mixing of the two types of code within the same process is not allowed. A 64-bit application cannot link against a 32-bit library (DLL) and similarly a 32-bit application cannot link against a 64-bit library. This may lead to the need for library developers to provide both 32- and 64-bit binary versions of their libraries.
SSIS supports these two different flavors of 64-bit in various levels.
Availability of SSIS executable in 32 & 64 bit architectures
| Architecture | SSIS runtime (dtexec) | BI Studio | DTS2000 Support (invoker) |
| x86 | Yes | Yes | Yes |
| x86-64 | Yes* | No* | No* |
| IA-64 | Yes* | No** | No* |
*SSIS installs 32bit binaries to take advantage of WOW64.
**SSIS does not install 32bit binaries, and thus this component cannot run even in 32-bit mode.
BI-Studio
Since the BI-Studio is only available in x86, due to the limitations of mixing the two types of code mentioned above, BI-Studio can only load 32-bit drivers (i.e. if you have a 64bit Oracle driver, it will not show up in the designer, that's why you'd need a 32-bit version of the Oracle driver during the design time).
Another point about the BI-Studio is that, although it is available in x86-64 and can be invoked using the WOW64 feature of windows, it is not available in IA-64 machines, and thus cannot be used (see above table). This limitation is enforced mainly because Visual Studio 2005 is not supported in WOW64 mode on IA-64 platforms. Since BI-Studio is derived from Visual Studio 2005, to avoid ending up in an unsupported platform, SSIS does not deploy 32-bit BI Studio on IA-64 platforms.
However, the SSIS runtime is available in both of these platforms, and therefore IA-64 machines can be used in production even without BI-Studio installed on them.
SSIS Runtime (dtexec.exe)
SSIS runtime is available in both flavors of 64-bit (see above table), along with the 32-bit binaries that enable running SSIS in 32-bit process space, using the WOW64 feature of Windows. If your SSIS application has a dependency on a 32-bit binary/driver/connector/script/DTS2000 Package, running the 64-bit dtexec.exe fails to load dependencies due to the limitation on mixed-mode application linking.
So, when invoking the 64-bit dtexec.exe, make sure that all the dependencies of your SSIS application are available in 64-bit. This might introduce some limitations around unavailability of some OleDB providers on 64-bit, or running DTS2000 packages, which can only be done in 32-bit. In such cases, you'll have to use the 32-bit dtexec.exe on a 64-bit machine.
Deniz Erkan
Program Manager - SQL Server Integration Services - Microsoft
More reading: http://msdn2.microsoft.com/en-us/library/ms141766.aspx
Note: This article has been ported over from http://ssis.wik.is/.
Note: If you were the owner of the above article, update it to the latest information or with other appropriate information.
See Also
- [[SQL Server Integrations Services - List of TechNet Wiki Articles]]