Developing SSIS packages for 64-bit Windows on 32-bit Windows

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.

Designing Integration Services Packages on 64-bit Computers

You cannot design packages in BI Development Studio or debug scripts in the Script task on Itanium-based operating systems.

When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.

The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use. For example, you run the package in the development environment on a 64-bit computer. By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True.

You cannot run packages that contain scripts—for example, in Script tasks or Script components—in 64-bit mode unless the scripts have been precompiled. By default, the value of the PreCompile property is True for both the Script task and the Script component.

You cannot use the Execute DTS 2000 Package task in a package running in 64-bit mode. The SQL Server 2000 Data Transformation Services runtime is not available in a 64-bit version.

Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job

When you configure a SQL Server Agent job with a step of type SQL Server Integration Services Package, the job always runs the package in 64-bit mode on a 64-bit computer, because this step type invokes the 64-bit version of dtexec.exe to run the package. This assumes that you have installed and are running the 64-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer. If you have installed and are running the 32-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer, this step type invokes the 32-bit version of dtexec.exe to run the package.

If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.

Entire list of 64-bit considerations for SQL Server Integration Services can be found on this post of Dougbert's Blog.

References: Wiki, Dougbert's Blog, MSDN BOL