SQL Server Agent Jobs for Packages
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.
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.
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.
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 run a package in 32-bit mode from a 64-bit version of SQL Server Agent, in the New Job Step dialog box, on the Execution options tab, select Use 32 bit runtime. |
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.
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 a video that demonstrates how to troubleshoot packages that are run from SQL Server Agent jobs, see the video home page, Troubleshooting: Package Execution Using SQL Server Agent (SQL Server Video), in the MSDN Library.
-
Knowledge Base article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step, on the Microsoft Web site
-
Video, Troubleshooting: Package Execution Using SQL Server Agent (SQL Server Video), in the MSDN Library
-
Video, How to: Automate Package Execution by Using the SQL Server Agent (SQL Server Video), in the MSDN Library
-
Technical article, Checking SQL Server Agent jobs using Windows PowerShell, on mssqltips.com
-
Technical article, Auto alert for SQL Agent jobs when they are enabled or disabled, on mssqltips.com
-
Blog entry, Configuring SQL Agent Jobs to Write to Windows Event Log, on mssqltips.com.
|
