Q
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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close