DTS package fails on connection to server

[SQLScholar]

Hey all,

I am getting really confused with an issue i am having with DTS.

If I compare 2 packages i have side by side this is what i can tell you. DTSA is the working one, DTSB is the failing one.

Both connect through the same user
Both do updates to the same database
DTSB does inserts to the database
DTSB creates files on the network and emails them.
Both email out mails to say they various stages fail/suceed. DTS B doesnt get that far.

Both Work when manually triggering it
DTSA works on schedule
DTSB doesnt work on schedule.

So DTSB fails with an error of

The job failed. The Job was invoked by Schedule 143 (Installation Creator). The last step to run was step 1 (Installation Creator).

Executed as user: domain\user. DTSRun: Loading... 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. Process Exit Code 1. The step failed.

Now i cant work it out - as i cant see how this could be security as it has the correct rights for stage, and the connection is identical to the DTSA.

Any ideas?

Dan


----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.
- Dr. Seuss
----------------------------------------

 

[SQLScholar]

BTW its SQL 2000

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.
- Dr. Seuss
----------------------------------------

 

[gradley]

A couple of things to consider!

1. When you say schedule, if you mean SQL Server Agent, the permissions for the account that starts up the SQL Server Agent service are used.

2. When creating a file to the network, are you using the UNC to the network folder or mapping a drive. You should be using the UNC ("\\Server\Folder\Filename") as mapped drives may not be seen by the service account.

There are several posts in the group that discuss how to run jobs from SQL Server Agent.

Good Luck!