Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jholton
Starting Member
2 Posts |
 Posted - 2004-06-01 : 14:30:48
|
| I have some DTS packages that are scheduled to run every night.They import a text file into SQL Server tables.Every night they fail with "Error opening datafile: The system cannot find the path specified." The file name is dynamic, and is specified in an ActiveX Script.However every morning I can come in and run the package manually and it is successfull. Just to be sure, I added some code to the package that writes that path and file name of the "datafile" and it is the same when it fails at night and works when I run it. Also, when the package runs at night, the file does exist.Any ideas?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 14:49:48
|
| The DTS package when scheduled runs from the database server and not your machine. The files need to valid from the database server's perspective. So when you design a DTS package on your machine and execute it manually, it runs from your machine. But when scheduled, it runs from the database server. So right before scheduling it, you need to pull up the package on the database server and make sure everything works from there.Tara |
 |
|
|
jholton
Starting Member
2 Posts |
Posted - 2004-06-01 : 15:02:58
|
| I'm doing all this on the server. I use Remote Desktop to connect to the server and manually run them. Every night, the datafiles are FTP'ed to the server. The server is the only place they exist.Any other ideas?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 15:04:23
|
| Does the account that the SQLSERVERAGENT logs on with have access to the files?Tara |
|
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2004-06-01 : 15:48:57
|
| To expand on what Tara is saying - try running the job that is failing under the username that it is run under bythe SQL Server Agent. If you're using NT authenication, this would involve connecting with Remote Desktop with this username and seeing what happens when you run each step individually from within the DTS designer. I solved a similar issue with one of my DTS packages only after I logged in under the account that the SQL Server Agent was using and ran the package step by step. |
|
|
|
|
|
|
Why does my DTS package fail when scheduled?