DTS Package run from schedule 1

[RangerFan]

We are attempting to run a DTS package at a scheduled time,
however, the job fails to run and gives us the following
error message:

The job failed. The Job was invoked by Schedule 33 (ohcprodDev). The last step to run was step 1 (ohcprodDev).

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217887 (80040E21) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

If the job is executed manually it runs fine. This is not the only job that fails. Jobs that had been running fine
as scheduled are now not running as well.

Any thoughts on this topic would be appreciated.

 

[tlbroadbent]

Check out the article "INF: How to Run a DTS Package as a Scheduled Job" at

http://support.microsoft.com/support/kb/articles/Q269/0/74.ASP

Terry

http://members.home.net/tlbroadbent/index.htm

 

[RangerFan]

I read the article and tried several of the proposed solutions. Nothing seems to work. These packages used
to run week after week with no problem. The only thing
that was done was one of the steps in the package was
changed. Ever since then the packages won't run as scheduled.

 

[tlbroadbent]

Check the owner of the job. Do you have other jobs that run as scheduled? Is the owner the same as the job that fails? Terry

http://members.home.net/tlbroadbent/index.htm

 

[RangerFan]

The owner in all cases is SA. It is the same name as the
login id for SQL Server agent.

 

[tlbroadbent]

Is your SQL Agent starting with the login sa? That means sa is setup as an NT login on the machine. Is that correct. That certainly isn't a normal setup. Terry

http://members.home.net/tlbroadbent/index.htm

 

[elmo29]

I had a DTS package that didn't run (with a very similar error to yours) - mine contained an exe passing a variable. It worked fine when you executed it normally but within a schedule it failed. This turned out not to be a problem with the users (the most obvious option) but in fact the variable being passed (which was a filename). It could cope just fine without it when you ran it normally - but for some reason failed within a schedule. I would think you have a similar problem

So check all paths you are using that the full path name is begin used.

Hope that helps

 

[RangerFan]

SA is the name found in the SysAdmin_Login ID in the SQL
Server Agent Properties\Connection tab.

We are using SQL Server Authentication here.

I have been told that in this case SA is the login for SQL
Server and not NT. Do you concur?

 

[tlbroadbent]

SQL Agent starts as an NT service. It has to run under an NT account. Terry

http://members.home.net/tlbroadbent/index.htm