DTS and Agent issue

Question

DTS and Agent issue

hoo-t

SSChampion

Points: 12713
More actions
June 4, 2004 at 7:31 am

#161597

A colleague has developed an application which uses a DTS package to load several tables. The DTS package uses several ActiveX tasks to retrieve the data. It works great when executed directly (from the server), but when executed through a SQL Agent job (whether directly or scheduled), it fails. The id it runs under is a local administrator on the box, and system administrator in SQL. The only thing I can come up with is that because the job executes DTS from the DTSRUN utility, that perhaps there is an issue there ("DOS" vs Windows)? Has anyone else faced this issue? Any suggestions?
Steve

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

danarivk Old Hand Points: 378 More actions · Answer 1

I've had the same problem. Try running the job with the SA user account. For some reason, the jobs will not run unless they use this account. I'm not sure why, I assume it's a Microsoft bug.

Dana

hoo-t SSChampion Points: 12713 More actions · Answer 2

Dana,

Thanks for the quick response!

I tried changing the job owner to sa, still failed. It should be noted that the DTS package is running under the agent startup account regardless of the job owner. This account is a domain account, local administrator, and has sa level rights.

Steve

danarivk Old Hand Points: 378 More actions · Answer 3

Hey, sorry. I remember having the same issue and the only way we could resolve it was by changing the owner of the job to SA.

I was just reading an article on microsoft's website. Maybe it will help (or you may have already read it)

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

If I think of anything else I'll let you know.

Dana

hoo-t SSChampion Points: 12713 More actions · Answer 4

Thanks, Dana!

I hadn't seen that before. I haven't read all the way through it yet, but it looks like a great "checklist" for diagnosing a DTS job.

Steve

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

log on to the server using your sqlagent account and then try to run the package from EM\edit dts package.

You'll get more detailed errorinfo.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

hoo-t SSChampion Points: 12713 More actions · Answer 6

alzdba,

Thanks, the problem is that it runs perfectly from the package itself. It even runs if I copy the dtsrun statement from the job and shell out to a command prompt and execute it there, and from the agent account. I've exhausted every possibility that I can think of and some that I couldn't think of, but others did. There is a thread on Microsoft's forums that addresses the same error message, but from a different application. Microsoft is looking at it. I think it may be a bug (activex related).

Thanks,

Steve

PhoenixDBA Mr or Mrs. 500 Points: 578 More actions · Answer 7

You say that the DTS is running from an application. If it is a VB app maybe this may help. It is Microsoft Knowledge Base Article 323685.

http://search.microsoft.com/search/results.aspx?st=b&na=88&View=en-us&qu=323685

Happy hunting

hoo-t SSChampion Points: 12713 More actions · Answer 8

Phoenix,

Thanks. No, its just scheduled through SQL Agent, but it contains activex scripts. Today, another colleague suggested trying to run it through a command exec batch file. Once again, if you execute the batch file from the command prompt, it works fine. But, if it executes from SQL Agent (scheduled) it fails. I'm really starting to think that activex and SQL agent just aren't playing nice.

Steve