Scheduling a Package

The primary way to schedule packages in SSIS is with SQL Server Agent, which ships with the SQL Server database engine. If you didn’t have a database engine in your environment, then you must use something like Task Scheduler, which ships with Windows. Scheduling a package with SQL Server Agent is much simpler and gives you much more flexibility.

The first step to scheduling a package is to connect to the database engine. Ensure that the SQL Server Agent service is started. Right-click Jobs under the SQL Server Agent tree and select New Job. The New Job dialog box will open, where you can create a new job.

In the General page, type the name of your job such as Execute Package. In the Steps page, click New, which opens the New Job Step dialog box. Type Execute Sample Package for the Step name property in the General page, as shown in Figure 9-14. Then, select SQL Server Integration Services Package as the type of step. For the time being, use the default SQLAgent Service Account as the Run as account. This means that the account that starts SQL Server Agent will execute the package, and sources and destinations in your package will use Windows Authentication with that account if they’re set up to use Windows Authentication.

Figure 9-14: The General page

For the Package source, select the SSIS Package Store and point to a valid SSIS service. Pick any test package that won’t have production impact by clicking the ellipsis button. When you click the ellipsis button, you’ll see all the folders in the package store, whether in the msdb database or the file system.

The rest of the options resemble exactly what you saw earlier in DTExecUI.exe, with the exception of the Reporting tab, since there is no console to report to from a job. Optionally, you can also go to the Advanced page to set the Include step output in history option to get more information about the job when it succeeds or fails. Click OK to go back to the New Job Step dialog box. You can then go to the Schedules page to configure when you’d like the job to run. Click OK again to go back to the main Management Studio interface.

With the job now scheduled, right-click the newly created job and select Start job at step. You will then see a status box open that starts the job. After you see a success, it does not mean the job passed or failed. Instead, it simply means that the job was started successfully. You can right-click the job and select View history to see if it was successful.

This opens the Log File Viewer (shown in Figure 9-15), which shows you each execution of the package. You can drill into each execution to see more details about the step below. The information this step provides is adequate to help you diagnose a problem, but you may need package logs to truly diagnose a problem.

Figure 9-15: Log File Viewer

You can find a great article at http://support.microsoft.com/kb/918760 that is updated on a regular basis and that will help you diagnose problems with scheduling packages.