DTS package runs manually but not scheduled, why ?

[rlatham]

I have created a DTS package under the dbo user (server administrator).

When I run this package manually, by clicking Execute Package, it drops a table with all the content, re-creates the table, does a text file import into the table and then sends an email to me. The package works fine.

When I schedule the package, it does not run at all. The package fails.

Please help me find the cause and the solution.

Thanks,

rlatham

 

[AlexCuse]

When this problem occurs, it is usually result of the SQL Server Agent account not having the necessary permissions to execute the package. I would check there first.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mutley1]

As Alex said, it's probably the agent permissions. If you have several stages it might be handy to find out at what point in the package it fails, so turn on error handling.

Open the package, then click on file / propertied and select the logging tab. at the bottom there is "error handling". Just put a vaild directory path in there with a TXT file at the end for your error log, e.g.

c:\myfolder\mssql\dtslog\mydtserrorlog.txt

Run the job then check the log.

HTH,

M.

 

[rlatham]

Thanks for your ideas...

Unfortunately, I cannot see how the SQL Server agent is setup, and the people I can ask are a pain in the neck.

I could not modify the original DTS package to set up logging, so I saved it with another name under my user.
Set up the log and executed it. It succeeded and the log was updated.

Then, scheduled the DTS package and it did not run at all. Not even the log got updated. The job failed.

What else can I try or do?

Thanks,

rlatham

 

[rlatham]

Are ddladmin role users allowed to schedule jobs ?

rl

 

[AlexCuse]

I'm afraid you are going to ask the 'pain in the neck' people. It sounds to me just like a permissions issue.



Ignorance of certain subjects is a great part of wisdom