RE: DTS Package fails when Scheduled

Do not use mapped drives. It will not work.
Use UNC (ie. \\Servername\sharename\folder\...\) instead.
Make sure SQL Server Agent account has the correct rights/permissions (ACLs).

First I would try setting the Access DB locally on the same box (with
appropriate ACLs), if this works move the DB to the network share (again
check ACLs).

"Chuck G." wrote:

Servando's explanation made a lot of sense, and I tried it with the same fail
message. After numerous attempts to tweak the system dsn, I decided to try
and change the context of the sql agent to run under the Domain/Administrator
account, which is also the owner of the DTS package. When I ran the
scheduled job under this context, I still received the error, even though I
ran the DTS manually seconds later. This account has full access to
everything on both machines, so I figured if it was the permissions being
pushed from SQL Agent that this would definitely work.

Here is another idea on what could be wrong. I have the folder where the
access database resides on the remote computer shared, and have mapped the
drive on my SQL machine. I am using this mapped drive to create my ODBC
connection. Is this the proper configuration?

"Servando Canales" wrote:

Make sure the ODBC connection is a system DSN source (for every account to
view and use)
Scheduled jobs run in the context of the SQL Server Agent service account
(whatever you specified : Local System, a user account, etc). Probably that
is why this account cannot contact your defined DSN source, or it does not
have the appropriate permissions to connect to whatever Database you defined
on your DSN

Servando


"Chuck G." wrote:

I am running SQL 2000. I have a DTS that imports an Access database from a
remote source into my SQL server. When I excecute the DTS manually it runs
perfectly, but when run as a Scheduled job I get the following error:

Executed as user: SQLSERVER\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: Copy Data from Results to Results Step
DTSRun OnError: Copy Data from Results to Results Step, Error = -2147467259
(80004005) Error string: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified

Obviously the ODBC connection is not at fault since the package ruins fine
when not scheduled. I am looking at permissions, as the DTS scheduled job
password is different from the Access ODBC password. It doesn't make a lot
of sense that the job password would be passed onto the ODBC, but I am at a
loss.

Any ideas?


.

Relevant Pages

  • RE: DTS Package fails when Scheduled
    ... Apparently I was unable to pass on the database password when runninig the ... Make sure SQL Server Agent account has the correct rights/permissions. ... scheduled job under this context, I still received the error, even though I ...
    (microsoft.public.sqlserver.dts)
  • Re: Problems with SQL Srv. Agent and Proxy Account
    ... I have tried with both the sa account and with Use Windows ... Authentication on the SQL Server AGent Connection tab. ... >>rights recording to INF: Reset Proxy and the ...
    (microsoft.public.sqlserver.security)
  • Re: cant find directory when package scheduled as job
    ... the batch file to map the drive. ... from the DTS designer but fails when it is scheduled as a SQL Server Agent ... under my windows account and the packages executes correctly when run via ...
    (microsoft.public.sqlserver.dts)
  • Re: Problems with SQL Srv. Agent and Proxy Account
    ... You need to go to the General Tab of the SQL Server Agent ... >I have tried with both the sa account and with Use Windows ...
    (microsoft.public.sqlserver.security)
  • Re: HELP!! SQL Server Agent not working.
    ... Some things to check - Are you running SQL Server in fiber ... >changed the SQL SERVER SERVICE account to use a Local NT User account ... >configuration for SQL SERVER AGENT was also done in Enterprise Manager. ...
    (microsoft.public.sqlserver.security)