Run Job / DTS Errors 1

[sevi]

When I run a package manually by right-clicking the package icon and selecting execute it works fine. But when I schedule the package as a job to run regularly, it fails giving the following message :

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Any ideas or thoughts?

 

[harryrich]

When you manually start a DTS job the file access credentials come from your Windows logon and the SQL server credentials are as specified in the Enterprise Manager connection for that server. If you didn't get a logon dialog when connectiong to the server, they come from your Windows logon.

When the SQL Server Agent starts a DTS job it uses the credentials specified in the SQL Server Agent properties.

Thus, when a tested job fails under the agent it is usually due to the fact that the agent is prohibited some database or file access which you are permitted.

 

[tlbroadbent]

Check the following link.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sevi]

Thanks for that.

I've sorted it now. The SQL Server was starting with a domain account and the SQL agent was starting using a system account. So I changed the SQL server account to start with a system account and the SQL agent to start with the domain account and everything seems to work fine. Does it make sense to do this?