Scheduling Package Execution in SQL Server Agent
You can automate the execution of SQL Server Integration Services 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. 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. 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 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 How to: Run a Package.
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 and Attaching 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 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.
To create a package execution job step
External Resources
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
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services