Learn more about SQL Server tools

   
   























Latest from MSSQLTips















Scheduling SSIS packages with SQL Server Agent

(SQL Server Agent)


Overview
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.

Explanation
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:

 

 

 

 







 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.