How to use the 64-bit SQL Server Agent to schedule a SQL Server 2005 Integration Services package

Consider the following scenario. You want to run a package in a 64-bit environment. Additionally, the package contains tasks that do not support the native Win64 environment. In this scenario, you must select the 32-bit version of the DTExec.exe utility to run the package. However, when you view the Job Properties dialog box or the Job Step Properties dialog box, you do not see an option that will enable you to use the 32-bit version of the DTExec.exe utility

How to schedule the 64-bit SQL Server Agent to run a package by using the 32-bit version of the DTExec.exe utility

To schedule the 64-bit SQL Server Agent to run a package by using the 32-bit version of the DTExec.exe utility, you must manually create a job step to run the package. Additionally, you must select the 32-bit version of the DTExec.exe utility to run the package.

To select the 32-bit version of the DTExec.exe utility to run the package, you do not have to type any command-line switches. Instead, you must only provide the path of the 32-bit version of the DTExec.exe utility. To do this, follow these steps:

1. Create a SQL Server Agent job. To do this, follow these steps:
   1. Start the SQL Server Agent service.
   2. Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
   3. Expand SQL Server Agent, right-click Jobs, and then click New Job.
   4. In the New Job dialog box, specify a name for the job and an owner of the job.
2. Create a job step. To do this, follow these steps:
   1. In the New Job dialog box, click Steps, and then click New.
   2. In the New Job Step dialog box, specify a name for the job step.
   3. In the Type list, select SQL Server Integration Services Package.
   4. On the General tab, specify the location of the package.
   5. On the remaining tabs, configure the settings of the job step.
3. Select the 32-bit version of the DTExec.exe utility to run the package. To do this, follow these steps:
   1. In the Type list in the New Job Step dialog box, select Operating system (CmdExec).
   2. In the Command box, type the path of the 32-bit version of the DTExec.exe utility. When you do this, enclose the path in quotation marks, and position the path in front of the existing command.
      
      Notes
      • The 32-bit version of the DTExec.exe utility is in the following folder:
        Drive\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
      • The following is an example of the contents of the Command box after you add the path:

        "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "C:\MyFolder\MyPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF 

   3. In the Run as list, select a proxy account to run the 32-bit version of the DTExec.exe utility. The proxy account that is enabled for the SQL Server Integration Services Package subsystem may not be available in the Run as list for the "Operating system (CmdExec)" subsystem.
      
      Note You can enable the proxy account for the "Operating system (CmdExec)" subsystem. To do this, follow these steps:
      1. In SQL Server Management Studio, expand SQL Server Agent, expand Proxies, and then expand SSIS Package Execution.
      2. Double-click the proxy account that you want to enable for the Operating system (CmdExec) subsystem.
      3. Under Active to the following subsystems, click to select the Operating system (CmdExec) check box.
         
   4. Click Advanced, and then click to select the Include step output in history check box.
      
      
      Note Follow this step only if you want to include the output of the DTExec.exe.exe utility in the job history of SQL Server Agent. You can use this output for diagnostics if the job fails.
      

Benefits of running a package in a 64-bit environment

If you run a package in a 32-bit environment, the package is limited to running within the 4-gigabyte (GB) constraint of the virtual address space. If you run a package in a 64-bit environment, the package may use more than 4 GB of the virtual address space. During memory-intensive processing, the lookup performance and the sort performance may be improved on a computer that has more than 4 GB of available memory.

Note By default, a 64-bit application can run within the 8-terabyte (TB) constraint of the virtual address space on X64-based systems. A 64-bit application can run within the 7-TB constraint on Itanium-based systems.

Limitations of running a package in a 64-bit environment

• If you have a package that contains script tasks or script components, you must compile the scripts in the script tasks or in the script components before you run the package in a 64-bit environment. By default, the value of the PreCompile property is set to True for script tasks and for script components.
• If you run a package in a 64-bit environment, you may be unable to connect from the package to as many data sources as you can from a package that is running in a 32-bit environment. Some .NET Framework data providers and some native OLE DB providers may not be available in 64-bit versions. For example, the Microsoft OLE DB provider for Jet is not available in a 64-bit version.
  
  Note The Microsoft OLE DB provider for Jet connects to Microsoft Office Access databases and to Microsoft Office Excel spreadsheets.
• You cannot run a package that contains the Execute DTS 2000 Package task in a 64-bit environment. This is true because the SQL Server 2000 Data Transformation Services (DTS) runtime is not available in a 64-bit version.