Scheduling an SSIS Package with SQL Server Agent

In this article I’m taking another break from Report Services and will cover how to schedule an Integration Services Package with SQL Server Agent. If you’ve never done it before, it can sound like a daunting task, but it’s actually easier than you think.

A couple of things before we begin: For you to be able to schedule an SSIS package with SQL Server Agent, you must have installed SQL Server Agent when you installed SQL Server. Also, make sure SQL Server Agent is running before you try and start any jobs. To start SQL Server Agent, go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. Under SQL Server Services, right-click SQL Server Agent and select Start if SQL Server Agent is not already running.

Open SQL Server Management Studio and connect to the instance of SQL Server you wish to schedule the job. The job we create will be used to execute the package or packages.

Expand the SQL Server Agent node. Then, right-click on Jobs and select New Job.

On the General tab, name the package, select an owner, and pick a category. Don’t forget to provide a description about what exactly occurs in the job. When another developer comes behind you to perform some kind of maintenance on the package, an accurate description will save them lots of time in trying to determine the exact purpose of the job.

Then on the Step tab, click New to add a new step to the job. This is where you will specify which SSIS package will execute on this step of the job.

After naming the step, click the drop down box under Type and select SQL Server Integration Services Package. You can execute an SSIS package from a file system location, a SQL Server, or an SSIS Package Store.

If you select File System, you can browse to the location of the SSIS package, which is what I am doing for this example:

From this screen you can also edit connection strings under the Data sources tab or add/edit configuration files under the Configurations tab, just to name a couple of the options you have available to you at this point. You also have a couple of options on the Advanced screen. You can specify the job step’s action when the step completes successfully or when it fails. The default setting for Success is go to the next step and the default for Failure is to fail the job. When you’re done, click OK to add the step.

Under the Steps tab you can add multiple to steps to execute multiple SSIS packages, T-SQL scripts, SSAS queries, or Active – X scripts, just to name a few.

Next, on the Schedule tab, you can specify when you wish the job to execute. A package can be scheduled to run on a recurring basis, only once, when the CPU becomes idle, or automatically when SQL Server Agent starts. I'm not going to get into when you should schedule a package, but just use common sense. You probably don't want to schedule an intense package on your production servers while your users are accessing those servers during peak hours. After scheduling your job, click OK. Just a note, scheduling your job is not necessary. You can always right-click the job, and click Start Job at Step… to begin the job at a certain step. For this example, I haven’t scheduled my package.

After executing your SSIS package manually, you should see this screen. 

Lastly, to view a job’s history, right-click the job and select View History. You can see all the job’s prior executions. You can also export the Job history to a .log, .csv, or .txt file.