SQL Server Agent Jobs for Packages

  • Article

You can automate and schedule the execution of SQL Server Integration Services packages by using SQL Server Agent. You can schedule packages that are deployed to the Integration Services server, and are stored in SQL Server, the SSIS Package Store, and the file system.

Sections in This Topic

This topic contains the following sections:

  • Scheduling jobs in SQL Server Agent

  • Scheduling Integration Services packages

  • Troubleshooting scheduled packages

Scheduling Jobs in SQL Server Agent

SQL Server Agent is the service installed by SQL Server that lets you automate and schedule tasks by running SQL Server Agent jobs. The SQL Server Agent service must be running before jobs can run automatically. For more information, see Configure SQL Server Agent.

The SQL Server Agent node appears in Object Explorer in SQL Server Management Studio when you connect to an instance of the SQL Server Database Engine.

To automate a recurring task, you create a job by using the New Job dialog box. For more information, see Implement Jobs.

After you create the job, you must add at least one step. A job can include multiple steps, and each step can perform a different task. For more information, see Manage Job Steps.

After you create the job and the job steps, you can create a schedule for running the job. However you can also create an unscheduled job that you run manually. For more information, see Create and Attach Schedules to Jobs.

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 Alerts.

Scheduling Integration Services Packages

When you create a SQL Server Agent job to schedule Integration Services packages, you must add at least one step and set the type of the step to SQL Server Integration Services Package. A job can include multiple steps, and each step can run a different package.

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

For more information, see Schedule a Package by using SQL Server Agent.

For a video that demonstrates how to use SQL Server Agent to run a package, see the video home page, How to: Automate Package Execution by Using the SQL Server Agent (SQL Server Video), in the MSDN Library.

Troubleshooting

A SQL Server Agent job step may fail to start a package even though the package runs successfully in SQL Server Data Tools (SSDT) and from the command line. There are some common reasons for this issue and several recommended solutions. For more information, see the following resources.

After a SQL Server Agent job step starts a package, the package execution may fail or the package may run successfully but with unexpected results. You can use the following tools to troubleshoot these issues.

  • For packages that are stored in the SQL Server MSDB database, the SSIS Package Store, or in a folder on your local machine, you can use the Log File Viewer as well as any logs and debug dump files that were generated during the execution of the package.

    To use the Log File Viewer, do the following.

    1. Right-click the SQL Server Agent job in Object Explorer and then click View History.

    2. Locate the job execution in the Log file summary box with the job failed message in the Message column.

    3. Expand the job node, and click the job step to view the details of the message in the area below the Log file summary box.

  • For packages that are stored in the SSISDB database, you can also use the Log File Viewer as well as any logs and debug dump files that were generated during the execution of the package. In addition, you can use the reports for the Integration Services server.

    To find information in the reports for the package execution associated with a job execution, do the following.

    1. Follow the steps above to view the details of the message for the job step.

    2. Locate the Execution ID listed in the message.

    3. Expand the Integration Services Catalog node in Object Explorer.

    4. Right-click SSISDB, point to Reports, then Standard Reports, and then click All Executions.

    5. In the All Executions report, locate the Execution ID in the ID column. Click Overview, All Messages, or Execution Performance to view information about this package execution.

      For more information about the Overview, All Messages, and Execution Performance reports, see Reports for the Integration Services Server.

External Resources

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.