Test if file exits before running DTS

[yanios]

Any help is appreciated.
How can use SQL to I test to see if the new text file exits before running a scheduled DTS import? If the new file is not there, the DTS import does not to run.
Must I run test thru DOS first? Don't have a clue.
Thank you.

 

[gradley]

You can use an ActiveX script in DTS that uses FSO (File System Object). I use this as an initial step for most of my packages to verify that the file exists first.

Ex:

Function Main()

Dim objFSO
Dim cFilePath
Dim cFileName

cFilePath = "<file path>"
cFileName = "<file name>"
 
  ' instantiate the Scripting Object
  set objFSO = CreateObject("Scripting.FileSystemObject")

  With objFSO
    If .FileExists(cFilePath & cFileName) Then
       ' set on Global Variable that file exists..
    End if
  End With
  Set objFSO = Nothing
  Main = DTSTaskExecResult_Success
End Function