Problem solve Get help with specific problems with your technologies, process and projects.

Getting scheduled DTS packages to run

Troubleshooting your DTS packages can be frustrating. In this expert response, Monitoring and Administration expert Kevin Kline discusses why a DTS package may run when executed but not when scheduled.

I have created a DTS package that runs a query from my SQL Server database and stores the result as a comma/quote-delimited file on another server. It works perfectly when I execute it (I am logged on as System Administrator). However, when I schedule the package to run, it fails and gives me an error message about permissions. I can't understand why it works when I execute it but not when I schedule it. It doesn't seem to matter whether I create the package on the server, log on to the server as administrator or whether I create it on my own PC logging on with my personal login as local administrator.
DTS packages run under the context of the user that invoked them, when they are executed from one of the online methods (DTS Designer, DTS Run, etc.). However, DTS packages run under the security context of the SQL Server Agent when they are executed on a schedule. Make sure you have created a Windows user account for the SQL Server Agent that includes SA or systems admin privileges.

Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.