DTS Package Problem

[taphonomist]

I'm having some trouble with a scheduled package with SQL Server 2000. The package basically accesses and pumps data from an Access file that basically tracks usage of some machines, which I'd like to have a daily log of. As seems to be a pretty common problem, I'm able to execute the package, so long as it's not scheduled. When I set up a scheduled package, it gives me a permissions error (Cannot open file ... Exclusively locked or permissions needed). I've tried a number of things and nothing seems to help. One possible thing is that I'm running enterprise manager on my machine rather than the server, but as I understand it, when you save a package, the info is saved on the server??

taphonomist

 

[taphonomist]

Quick update: Using remote desktop, I ran the package (on the server) that I'd designed on my machine with no problems, but it still won't execute if scheduled.

taphonomist

 

[mercurymik]

When you run from your local machine you are using connections that are defined on your computer. When you schedule a package it runs with the connections on the server. You need to make sure your connections are created on the server and named the same as on your computer. I mostly use the ODBC connections and make sure they are System DNS ODBC's

Hope that helps

 

[taphonomist]

It turns out that the Server Agent was running under the local system account, which has no permissions for anything. With some fairly targeted googling I found a slew of people with similar problems, most of which appear to be related to the account that the Server Agent was running under. Doh!!! Just to be clear, to solve the problem, just right click on SQL Server Agent in Enterprise Manager, go to Properties, then under the General tab it displays which account the Server Agent is running with. Change that to an account that has proper permissions, and all is good.

taphonomist