I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.
Are you saying that if the process fails, the old files still exist. Do the filenames change - and what about date/time for the files ? Also, what would happen if you deleted or moved the files when you had successfully imported the data ? And what are you using now to import the data ?
Yes, the files are overwritten nightly. I am using a DTS package to import from the textfile. I thought about just deleting the files after the import, then the jobs would just fail if no files were there, but I just wondered if there was a way for SQL to recognize the date on the file before running the job.
Just created it and it seems to be working, but I have it setup only to see if the file exists, if it doesn't then the package fails, which is what I want. Is there a way to have it check for file date and fail if it is not correct instead of just if it exists?