Now that you’ve created and successfully deployed your SSIS package to run, it’s time to schedule it to run on your server so that it doesn’t need to be manually executed. This can be helpful in situations when the package being executed needs to be done at night, or over a weekend, so as to not hog all the memory of your machine during execution, or simply when the user doesn’t want to have to execute it manually.
The first step is to open Microsoft SQL Management Studio and log into the database engine, then expand the SQL Server Agent. An important note, if you do not have the appropriate permissions to schedule a job, you will find this very difficult. Also, if you are creating the job for another user, if they don’t have the appropriate clearance to run the package themselves, it will also not work.
From here, we’re going to create a new job. The job is what allows the user to schedule the package to execute automatically. To do this, you can right click the “Job” folder and select “New Job.”
Create a meaningful name for the job, as well as a good description to remove any doubt as to what the deployed package does. Three or four months from now, after this has been stricken from your memory, it is comforting to know what the job does without having to do more than looking at the name.
Now, you will create a new step. Click “new,” then name the step and click the drop down list for “Type.” The true functionality of a SQL Server Agent job is apparent here as you can select from over a dozen different types of steps, from TSQL scripts to command line executions. For the purpose of keeping it on SSIS, we will select the SQL Server Integration Services Package from the drop down list and then, on the General tab, enter the source, server, and credentials for the SQL Server you wish to deploy to. Then, select the package and click “Ok,” to exit the new step screen.
Ok, so we have the package selected as a step in the job, now we need to tell the job when to run. This is done by selecting the “Schedules” tab and clicking “New…”
Again, we name the schedule (there can often be several schedules, so naming each with a meaningful name will be helpful), select the type from “Recurring,” “One Time,” “Whenever the CPUs become idle,” or “When SQL Server Agent starts.” Each schedule type has a purpose, and your selection obviously depends on what you’re trying to do with your package and what the requirements of it will be. For today, though, we’re going to select recurring. Then, Sunday at midnight, with a duration of “No end date.” Once we click “Ok,” and then “Ok,” again, our job is scheduled and ready to go!