How to: Run a Package Using a SQL Server Agent Job

  • Article

This procedure describes how to create a step in a SQL Server Agent job that runs an Integration Services package. You must create a separate job step for each package that you want to run.

A job is associated with one or more schedules. For information about how to schedule the job to run the package specified in the job step, see How to: Schedule a Job (SQL Server Management Studio).

The account that runs an Integration Services package as a SQL Server Agent job step requires all the same permissions as an account that runs the package directly. For information about how to troubleshoot packages that are run from SQL Server Agent jobs, see the Microsoft Knowledge Base article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.

For information about how to run a package immediately, see Running Packages.

To create a job step that runs a package

  1. In SQL Server Management Studio, open the instance of SQL Server in which you want to create a job, or the instance that contains the job to which you want to add a step.

  2. Expand SQL Server Agent and do one of the following:

    • To create a new job, right-click Jobs and then click New.
    • To use an existing job, expand Jobs, right-click the job, and then click Properties.

  3. On the General page, if you are creating a new job, provide a job name, select an owner and job category, and, optionally, provide a job description.

  4. To make the job available for scheduling, select Enabled.

  5. Click Steps, and then click New.

  6. Provide a step name and, in the Type list, select a job step type that is based upon the version of the dtexec utility (dtexec.exe) that you want to run the job:

    • To run the job by using the version of the dtexec utility that automatically gets invoked by the system, select SQL Server Integration Services Package.
      On a 32-bit computer that is running SQL Server and SQL Server Agent, this setting invokes the 32-bit version of the dtexec utility.
      On a 64-bit computer that has the 64-bit version of SQL Server and SQL Server Agent installed and running, this setting invokes the 64-bit version of the dtexec utility.
      On a 64-bit computer that only has the 32-bit version of SQL Server and SQL Server Agent installed and running, this setting invokes the 32-bit version of the dtexec utility.
    • To run the job in a non-default version of the dtexec utility, select Operating system (CmdExec), and then enter a command line that invokes the specific version of the dtexec utility. To create the command line that you can then paste into the job step, you can use the Execute Package Utility (dtexecui.exe).
      For example, you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode. For Type, select Operating system (CmdExec), and then enter a command line that invokes the 32-bit version of the dtexec utility.
    • To run SQL Server 2000 DTS packages from a SQL Server Agent job, select Operating system (CmdExec), and then enter a command line or use a batch file that invokes the dtsrun utility (dtsrun.exe). To create the command line that you can then paste into the job step, you can use the Execute Package Utility (dtexecui.exe).

  7. In the Run as list, select the proxy account with the credentials that the job will use. For more information, see the following topics:

  8. On the General tab, select the package source. If the source is SQL Server or the SSIS Package Store, provide the server name, select the authentication mode to use, and then provide the name of the package, or click the ellipsis button (…) and select the package. If the source is the file system, provide the path and name of the package, or click the ellipsis button (…) and locate the package.

    Important

    For password-protected SSIS packages, click the Configurations tab to enter the password in the Package Password dialog box. Otherwise, the SQL Server Agent job that executes the password-protected package will fail.

  9. To specify command-line options, do the following:

    • Click the Command Files tab to specify the files that contain the options that the package uses.

    • Click the Command Line tab to edit or restore the command-line options.

    • Click the Configurations tab to add configuration files and, optionally, export configured variables to a file.

    • Click the Data Sources tab, select the connection manager check box and then update the connection string.

    • Click the Execution Options tab to specify the package run-time behavior such as whether the package fails if warnings occur.

    • Click the Logging tab to add log providers. Select a log provider in the Log Provider list and then type the connection string.

    • Click the Set Values tab to map properties and variables to values.

      Note

      The property path uses this syntax: \Package<EM><container name>.<property name>. Depending on the package structure, a container may include other containers, in which case nested containers are separated by a back slash (). For example, Package\MyForeachLoop\MySequence\MyExecuteSQLTask.Description.

    • Click the Verification tab to specify whether only signed packages can run and to specify the version of package to run.

  10. Click OK.

See Also

Tasks

Scheduling Package Execution in SQL Server Agent

Other Resources

Administration How-to Topics (SSIS)

Help and Information

Getting SQL Server 2005 Assistance