Hi all;

I’m not sure if this was posted before but here is the issue, I have a DTS that loads a .txt file into a table, my source file connection points to the path file “c:\data_file\users.txt” and I also have an ActiveX script that checks if the file with that name is not in that folder then it stops the DTS.

What happened was I used another file with this name “users.txt.tmp” and I was expecting the DTS to stop processing as the filename doesn’t match “users.txt”, but guess what, I was surprised that it loaded the file.

So, my question is, why the DTS ignored the second extension (.tmp) as if its not there and loaded the file? Is it a bug is SQL DTS that it takes the first extension and ignores the remaining ones?

I’m kind of stuck and don’t understand why that happened.

Your thoughts are really appreciated

Thanks
Mohammad Musleh

Database Analyst/Arch

What wildcards are being used in the filename's check for existance?

--Jeff Moden

Here is the ActiveX code I use to check for the existence of the file

Set objFSO = CreateObject("Scripting.FileSystemObject")

’’’ ImportPath = c:\data_file
’’’ ImportFile = users.txt
’’’ ImportPath and ImportFile are Global Variables

ImportPath = DTSGlobalVariables("ImportPath").Value
ImportFile = DTSGlobalVariables("ImportFile").Value

ImportFullPath = ImportPath & ImportFile

If objFSO.FileExists(ImportFullPath) Then
    Main = DTSStepScriptResult_ExecuteTask
Else
    Main = DTSStepScriptResult_DontExecuteTask
End If

Set objFSO = Nothing


Database Analyst/Arch

to make things easier on you guys, here are the mains steps I have in the DTS

I have the following global variables
ImportPath, which equals to “c:\data_file” and
ImportFile, which equals to “users.txt”
FileExists, which equals to False “this is a flag I use either to move forward with the DTS or to stop it”

(1)
First thing I check in my DTS is the existence of the file that I’m loading by setting the flag “FileExists” to True or False

Function Main()
Dim objFSO, strDataFileName, ImportFullPath

Set objFSO = CreateObject("Scripting.FileSystemObject")

ImportPath = DTSGlobalVariables("ImportPath").Value
ImportFile = DTSGlobalVariables("ImportFile").Value

ImportFullPath = ImportPath & ImportFile

If objFSO.FileExists(ImportFullPath) Then
DTSGlobalVariables("FileExists").Value = True
Else
DTSGlobalVariables("FileExists").Value = False
End If

Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function

(2)
Then I have an “Execute SQL Task” to clean up a holding table if it has data, in the workflow properties for this task, I have the following ActiveX code

Function Main()
If DTSGlobalVariables("FileExists").Value = True Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End Function

(3)
I load the file into a holding table

Then I’m done.

Thanks

Database Analyst/Arch

Um... shouldn't the following ...

ImportFullPath = ImportPath & ImportFile

... look more like ...

ImportFullPath = ImportPath & '\' & ImportFile

???

--Jeff Moden

you are right Jeff, I'm using "\" when I define the global variable, so my global variable ImportPath equals to “c:\data_file\”

Database Analyst/Arch