DTS Job fails when scheduled

I have a DTS local package that transfer data from the host SQL server to another. If I run the package directly (i.e. Right click - Execute Package) everything works as it should.

However, if I try and execute this as a Server Agent job it fails, the job history step details shows the following error:

Executed as user: MYSERVERNAME\SYSTEM. ...: Drop table customer Step DTSRun OnError: Drop table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Drop table customer Step DTSRun OnStart: Create Table customer Step DTSRun OnError: Create Table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server... Process Exit Code 2. The step failed.

Whilst this is obviously a connection issue, I dont understand how this can work if executed directly and not if executed as an agent job.

Can anybody shed any light on this?

If it helps this is the sequence of events my DTS package uses

1) Connect to remote DB
2) Drop table customers
3) Create new remote customers table
4) Connect to to local DB (hosting the DTS package)
5) Select * from local customers table
6) Dump data from local select into remote DBIt's a frequently asked question. When you open a DTS package from your workstation, the environment is that of your workstation.

When you execute a DTS package from a scheduled job, the environment is that of the server.

If there are differences between the server environment and the environment of your workstation, then the executed package will fail.

For example, if you have Oracle drivers configured on your PC (but not on the server) you will be able to execute the DTS package from your PC, but not from the server.

Try using Terminal Services to remote in to the DB Server. Then open the package and execute it from the server desktop. The errors become more apparent at that point.

Regards,

hmscott|||If you allow mixed mode authentication, create an login on your target server for use as a service account, then define credentials for your individual connections in the "Connection Properties" dialog for each specific server connection you intend to use.

Alternately, you can setup your target server as a linked server and specify which credentials it is to use when operating on the remote server in the "Security -> Linked Servers" node in Enterpise Manager.