Package Schedule

By using the SQL Server Agent, there can be scheduling of packages to run automatically. For the packages to be scheduled, it is necessary to store them either in the MSDB or the file system in the SSIS package store.

Steps for Scheduling

Let us suppose that for updating the status, there is a need to run the status at 6 pm everyday. Then the following steps are required for scheduling a package for execution:

1. Open SSMS.
2. Connect to the SQL Server Database Engine.
3. Right-click on SQL Server Agent and select New, Job.
4. In the General options page, enter the name of the job, in this example Daily Patient Status Update.
5. Select the Steps option page.
6. Click New for creating a new step.
7. Enter the Step name “Update Patient Status”.
8. In the Type pull-down, select SSIS Package.
9. In the Package Source pull-down, select the SSIS package store.
10. In the Server pull-down, select the server name.
11. Click on the Package selection button to the right of the Package field.
12. Browse the “Select an SSIS Package” window to find the package imported earlier.
13. Click OK.
14. Click OK for saving the step.
15. Select the Schedules option page.
16. Click New for creating a new job schedule.
17. In the Name field, enter “Daily at 6pm”.
18. In the Occurs pull-down, select Daily.
19. Change the Daily Frequency to 6:00:00 PM.
20. Click OK for saving the schedule.
21. Click OK for saving the job.

Now everyday at 6 pm, the SSIS package will be run by the job. The job saved in the database can be reviewed in the Jobs folder located within the SQL Server Agent. This can also be tested by right click on the Job and then select Start Job.

Package Series

A series of packages in sequential steps can be run by the jobs. A bigger task can thus be completed by such chaining of the packages together.