Scheduling Package Execution in SQL Server Agent

  • Article

You can automate the execution of SQL Server 2005 Integration Services (SSIS) packages by using SQL Server Agent. SQL Server Agent is the Windows service that lets you automate tasks by running SQL Server Agent jobs. SQL Server Agent must be active before local or multiserver jobs can run automatically. For more information, see Configuring SQL Server Agent.

To automate package execution, you create a job using the New Job dialog box that SQL Server Management Studio provides for SQL Server 2005. For more information, see Implementing Jobs.

After you create the job, you must add at least one step and set the type of the step to SQL Server Integration Services package execution. A job can include multiple steps, each running a different package. A SQL Server Agent job step can run Integration Services packages that are saved to the msdb database or to the file system. For more information, see Creating Job Steps.

Note

On a 64-bit computer, the SQL Server Integration Services job step type means that the job step will run the package in 64-bit mode. To have the job run the package in 32-bit mode instead, change the job step type to Operating system (CmdExec), and then have the job step invoke the 32-bit version of the dtexec utility (dtexec.exe). For more information, see How to: Run a Package Using a SQL Server Agent Job.

Running an Integration Services package from a job step is similar to running a package using the DTExec and DTExecUI utilities. Instead of setting the run-time options for a package using command-line options or the Execute Package Utility dialog box, you set the run-time options using the New Job Step dialog box. For more information about the command-line options for running a package, see dtexec Utility.

After you add the job and the job step, you must create a schedule for running the job. For more information, see Creating Schedules.

You can enhance the job by setting notification options, such as specifying an operator to send an e-mail message to when the job finishes, or adding alerts. For more information, see Defining Alerts.

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.

To create a package execution job step

See Also

Concepts

Integration Services Considerations on 64-bit Computers

Other Resources

Administering Integration Services

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
New content:
  • Added note about how to run a package in 32-bit mode on a 64-bit computer.
  • Described the permissions that are required by the account that runs the package.