Using SQL Server Agent to Execute Packages

It's common to use SQL Server Agent to schedule and execute packages on the server. With Agent Proxies and Subsystems, it's simple to set up a secure job for executing a package. The following is a quick-start guide to setting up a job:

1.	In Management Studio, open the instance of SQL Server in which you want to create the job.
2.	In the SQL Server Agent node, right-click on Jobs and select New.
3.	On the General tab, provide a name for the job, and select an Owner and Category, as shown in Figure 17.13. Figure 17.13. Create a new job
4.	Make sure the Enabled option is checked so you can schedule the job.
5.	Click the Steps tab and then click the New button. The New Job Step dialog box opens, as shown in Figure 17.14. Figure 17.14. Create a new step
6.	Give the step a name.
7.	In the Type list, select SQL Server Integration Services Package.
8.	In the Run As combo box, select the proxy account. This example has the SQL Agent Service account selected. You can create a different proxy account if you create the credential and proxy.
9.	On the General tab, select the package source and package to execute.
10.	You can set the command line, execution, and other options here as well. This dialog box is the same as for the DTExecUI utility.
11.	Click the OK button.

You can set other settings in the dialog boxes, but this is the basic setup and should be enough to get you started. For more information about setting up jobs, credentials, and proxies, search Books Online or MSDN.

Caution

Integration Services can be installed on a machine independent of SQL Server. However, the SQL Server Agent service relies on the SQL Server service. If you intend to use SQL Agent to schedule package execution, you need to have SQL Server installed on the machine as well. If you have some policies that don't allow SQL Server on the same box with Integration Services or otherwise govern the installation of SQL Server that prohibits you from running both servers on the same machine, you need to use the Windows Task Scheduler or some other such tool to schedule package execution.