SQL Server DTS and Job Agent problems

[mmcardle]

I am developing a data-warehouse that lifts data from an Informix database using SCO Vision ODBC drivers and populates a "data staging area" in SQL Server 2000.

I've created meta data services packages for the DTS bit and these work when run on their own. When I schedule the DTS packages to run sequentially as a job, more often than not, they don't. I come in the next day and the job is stuck on one step (sometimes the first one). No error messages at all on SQL Server or the server that the Informix database resides on). However, the DTS packages will run OK.

Any ideas?

 

[tlbroadbent]

Does the SQL Agent account have permissions on he Informix database?

When you run a package yourself from Enterprise Manager, it runs under your SQL Login. When it runs as a job, it runs under a SQL Agent account. Review of the MS KB article at the following link may help.

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

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mmcardle]

Thanks for your comments Terry.

I've already looked at this article. The problem is that I've come in some mornings and the whole job has run without any problems. Some mornings I've come in and it's stuck on step 14 (or whatever) of the job. I've sometimes managed to manually run the whole job during the day and it's worked fine, but it has then got stuck when scheduled to run overnight. There seems to be no rhyme or reason to it! I posted this query in case someone else had suffered from this.

As it stands, I'm certain it isn't a permissions issue as I've managed to run the whole thing from time to time. But, I don't know if it's an issue with Windows 2000, SQL Server 2000, the SCO ODBC drivers, SCO OpenServer, or the Informix database!

 

[tlbroadbent]

I see. I wasn't clear on the fact that the Job had run successfully. We have encountered a similar problem with one JOB in particular and still don't have a handle on the reason for the failure. This JOB is running on SQL 7. I'll post something here if we find a solution. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.