Unanswered: Verify data source viable before proceeding
I've completed my first SQL project, for which I've built a DTS Package. First thing it does it drop all records in the destination table, before importing new records from a txt file and then massaging them.
After I got done, I realized that if the data source is not available for some reason, the records will still be dropped, the process will fail, and the destination table will be left empty. In this case, leaving the existing records intact would be preferable to not having any.
How can I test that the txt file exists before dropping the records?
ps: Users will maintain a link to the table. I plan to update the table after business hours. If someone happens to have their linked application open while I'm trying to update the table, will it fail?
I am using the following mechanism in my DTS packages (see the atachement)
You "Dir ... > Filelist.txt", then import the content of Filelist.txt in a temporary table. You select the first line of the table and output the parameter in a global variable. Then, an ActiveX task checks that the variable is not null, that is the source text file exists
As marp says:
Add an ActiveX module @ the beginning of the Package.
Using VBS and the FSO object, test for the existence of the filename (see code below).
Then connect this module to the next one with an 'On Success' workflow.
Set fso = CreateObject("Scripting.FileSystemObject")
IF fso.FileExists(<filename+path>) = FALSE THEN
Main = DTSTaskExecResult_Failure
Main = DTSTaskExecResult_Success