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

Article translations Article translations
Article ID: 934653 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

Consider the following scenario. You use the 64-bit SQL Server Agent to schedule a Microsoft SQL Server 2005 Integration Services (SSIS) package to run as a job step in a 64-bit environment. In this scenario, the 64-bit SQL Server Agent will always use the 64-bit DTExec.exe utility to run the package. However, some tasks do not support the 64-bit DTExec.exe utility. Therefore, the package cannot always run. In some cases in which the package cannot run, the following error message is logged:
This task does not support native Win64 environment. Please run the package in 32-bit WOW environment instead.
Note This error message is logged only if you have enabled logging in the package.

Additionally, some OLE DB providers and some ADO.NET providers are not available in 64-bit versions. If these providers are used in the package, you experience various connection problems when the 64-bit SQL Server Agent runs the package by using the 64-bit DTExec.exe utility.

MORE INFORMATION

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.

REFERENCES

For more information about how to run a package in a 64-bit environment, visit the following Microsoft Web site, and then see the "Integration Services considerations" section of the white paper that you download from the Web site:
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
For more information about how to create a CmdExec job step, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-US/library/ms190264.aspx
For more information about the limitations of running a package in a 64-bit environment, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms141766.aspx
For more information about how to create a proxy, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms190698.aspx
For more information about SSIS packages, click the following article number to view the article in the Microsoft Knowledge Base:
918760 An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

Properties

Article ID: 934653 - Last Review: November 20, 2007 - Revision: 1.2
APPLIES TO
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Keywords: 
kbexpertiseadvanced kbhowto kbinfo KB934653

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com