Monday, March 30, 2009

How to schedule a job that runs an SSIS Package in SQL Server

Again I just want to document how I did it. It's hard to remember sometimes the steps taken to make things work. Specially for things that are not done everyday. In this particular case, it is confusing which user accounts or login to specify to be used by the server agent. Anyway, here's a recipe on how to make this work, just follow along...

1. Create a credential.
2. Create a proxy under SSIS Package Execution using the above credential.
3. Create a job in SQL Server Agent, and this is the important part, specify "sa" as the owner of this job.
4. Now add a Step, select SQL Server Integration Services Package type. Again this is the crucial step: Go to the Run as dropdown, then select the proxy specified above.

Now test your package and let me know if it works for you.