DTS not running with schedule

Question

DTS not running with schedule

CGSJohnson

SSCertifiable

Points: 7208
More actions
April 25, 2006 at 11:34 am

#67428

I have a DTS package that is not running when it is scheduled. It fails when it is exporting data to an Access database. However, if I manually run the job, whether it be manually running each step or just hitting "Run" in the DTS designer view, it works fine. Any ideas? Thanks.
Chris

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

If this is a scheduled job, there should be more information in the job history. Check there and in the SQL and application logs for error messages. What account is the job set up to run under? When you execute the job or the steps using the DTS designer, it is executing as whichever user you are logged in as. When you schedule it as a job, you need to define which user you want to run the job as. Many times when a package runs fine in DTS, but fails when it is scheduled, it is a permissions problem.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 2

It runs as an administrator account (I know...bad), but there are no problems with permissions. What the job history shows is that it is trying to make a connection to a database that doesn't exist, although it doesn't exist in the job.

Ya know what?! I think that I just solved my problem. Could it be that the schedule was created pointing to an old copy of the DTS package? The old copy of the DTS package might have pointed to the database that does not now exist. Furthermore, I recently changed the schedule from running DTS...(whatever SQL codes in there) to English (DTS /S.../n...).

I'll let ya know how it turns out.

Thanks...Chris

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

No...that wasn't it. Anyone else have any other ideas? Thanks.

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 4

Do you have a full UNC path to the database. If you use a mapped drive, the job will work when run manually, but not when it is scheduled to run becuase the agent doesn't recognize the mapped drive. Try a full UNC path //server/folder/folder/db

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 5

Yeah...been burned by that in the past, Jim, so I'm on top of that. Like I said, when it runs, it looks like it is pointing at a database that is not present (not the database that a connection is made to in the package design). Thanks.

Joanie Jeffcoat SSC Veteran Points: 267 More actions · Answer 6

We've also encountered that in the past as well. We have a "farm" of servers and, depending on who designed which packages (legacy...our standards change when we find this stuff), we must remote in to that particular server in order for the package to run successfully if we need to re-run it for some reason. Also, if you (or someone else) copied over the DTS package (think dev to prod) then sometimes you have to ensure that the GUID is pointing to the instance of the different steps within the package ON THE RIGHT SERVER. I had that problem just the other week - I was refining a DTS package and the TRUNCATE TABLE routine was still pointing to PRODUCTION although my connections were still pointing to the dev servers! EEKS! (No worries...I was able to re-run the DTS package to repopulate.) If you have that package running on different servers, just make sure that the GUIDs are pointing to the right server. Make sense?

By the way...does anyone have a "foolproof" method of migrating DTS packages to avoid this???

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

So, I figured out what was wrong...and as usual...it was user error. I changed the database which I was updating to have a linked table. The error message generated was referring to the linked database. Thanks for all your replies.