Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Scheduling SSIS packages with SQL Server Agent


SQL Server Agent includes the SQL Server Integration Services Package job step type which allows you to execute an SSIS package in a SQL Server Agent job step. This can be especially handy as it allows you to schedule the execution of an SSIS package so that it runs without any user interaction.

To begin open SSMS, connect to the Database Engine, and drill down to the SQL Server Agent node in the Object Explorer.  Right click on the Jobs node and select New Job from the popup menu.  Go to the Steps page, click New, and fill in the dialog as shown below:

In the example above the SSIS package to be executed is deployed to SQL Server (i.e. the MSDB database).  You can also execute packages deployed to the file system or the SSIS package store.

Note that the Run as setting is the SQL Agent Service Account.  This is the default setting although from a security standpoint it may not be what you want.  You can setup a Proxy that allows you to give a particular credential permission to execute an SSIS package from a SQL Server Agent job step.

The first step to setting up the proxy is to create a credential (alternatively you could use an existing credential).  Navigate to Security then Credentials in SSMS Object Explorer and right click to create a new credential as shown below:

Navigate to SQL Server Agent then Proxies in SSMS Object Explorer and right click to create a new proxy as shown below:

You must specify the credential and check SQL Server Integration Services Package.  Now when you create or edit a SQL Server Agent job step, you can specify the proxy for the Run as setting as shown below:





Last Update: 10/22/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, December 18, 2017 - 10:58:27 PM - Tom Stricker Back To Top

How come you didn't complete the scheduling package (time, frequency, alert-notifications, etc.). You ended the SQL  Job step in the middle, then on the next page jump to downloading SSIS sample files.


Learn more about SQL Server tools