Does a SQL Server job always run on the server, or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?
The job in question calls a DTS package. The package succeeds when run locally; the job fails whether run manually or scheduled.
Does a SQL Server Job always run on the server or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?
For TSQL, Agent uses its own login (Windows authentication), and then uses EXECUTE AS LOGIN = 'n' command to simulate you. For other job step types, Agent uses Proxy and Credentials. You can trace this using Profiler, and even run a jobstep having WAIRFOR and check using sp_who etc.
Unless job owner is sysadmin, where no impersonation is performed (Agent will be "himself"). You can "trace" this ouputting result from whoami.exe (or similar utility) to an output file.
Refer to SQL Server Agent jobs and user contexts by Tibor Karaszi for more details.
I will need more info to your question to answer the 2nd part (with the exact error message).
When a SQL Server agent job is run it is run on the SQL Server machine not on your workstation.
It sounds like you don't have the drivers setup correctly on the SQL Server.
Keep in mind that if you are using a 64bit machine you'll need 64bit drivers and god only knows how DTS will handle 64bit stuff.
