Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 59

Scheduling Packages

Now that you’ve learned how to run packages manually, running packages through a schedule is easy. You can schedule them as a job to run automatically through SQL Server Agent or, alternatively, through a third-party scheduler. SQL Server Agent runs jobs under its own Windows account, which can pose some security issues when it comes to accessing components in your package. For example, you may have a package that uses Windows Authentication to access a database. When the package is run through SQL Server Agent, Agent will pass its credentials to the database, which may not be adequate to access the connection. To fix those issues, you can also run packages under a separate Windows account called a proxy account.

To schedule a package through SQL Server Agent, open Management Studio and expand SQL Server Agent > Jobs. Right-click Jobs and select New Job. Name the job something that you’ll recognize at a later time and then go to the Steps page on the left bar and click New at the bottom. This opens the New Job Step dialog box shown in Figure 59-1. A step is the smallest unit of work in a job, and it can have a number of different types. For SSIS, the type of job is SQL Server Integration Services Package, so that is the type you select for your step. Next, point to the package you’d like the step to execute, as shown in Figure 59-1. Notice that you can execute packages from the SSIS catalog (for those in the project deployment model) and the msdb ...