RE: DTS Package fails when Scheduled

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: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... If you have a manchine called anthony-devbox, ... and that machine has an account in the Directory Serivces, ... and, each one of them, in order to recreate the identity of the security ... The ODBC connection can be configured to use the current-executing-code ...
    (microsoft.public.win32.programmer.wmi)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... If you have a manchine called anthony-devbox, ... and that machine has an account in the Directory Serivces, ... and, each one of them, in order to recreate the identity of the security ... The ODBC connection can be configured to use the current-executing-code ...
    (microsoft.public.windows.server.security)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... The problem I have now is in modifying the ODBC connection. ... "With SQL Server authentication using a longin ID and password entered by ... won't the ODBC connection use the LocalSystem account since that is what WMI ... > Starting Win2000, the machine accounts are security principals, ...
    (microsoft.public.win32.programmer.wmi)
  • Re: W2K/WMI service (WinMgmt.exe) accessing an ODBC connection
    ... The problem I have now is in modifying the ODBC connection. ... "With SQL Server authentication using a longin ID and password entered by ... won't the ODBC connection use the LocalSystem account since that is what WMI ... > Starting Win2000, the machine accounts are security principals, ...
    (microsoft.public.windows.server.security)
  • Re: Task Scheduler
    ... account as the foreground account, ... > The scheduled job is Backup of directory on local drive to another local ... >> insufficient access rights to a network resource then it will ...
    (microsoft.public.windows.server.general)