Checking for a File

• Jason Delaune

  SSCrazy

  Points: 2649

  More actions

  November 12, 2001 at 11:56 am

  #104529

  Hello all,

  I have created a DTS package that uses the FileSystemObject option to check for a file. However, it fails everytime regardless if the file exists or not. Is this the best way to automate checking for a file? If so, what could be causing this to fail when the file exists?

  Jason

  BTW - I used the FolderExists method to check if the folder where the file is located exists and it works fine everytime.

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  November 12, 2001 at 12:09 pm

  #420014

  How about posting your code? I'd suggest checking it separately, perhaps in a .vbs file. You can throw in some msgbox's to help you see what is happening.

  Andy

  Andy
  Connect with me on LinkedIn

• David Benoit

  SSC-Dedicated

  Points: 34562

  More actions

  November 12, 2001 at 12:13 pm

  #420016

  Jason,

  I have found two things. First, you can use a VBScript step to check for the file. Example as follows;

  '**********************************************************************

  ' Visual Basic ActiveX Script

  '************************************************************************

  Function Main()

  Dim fso 'File system object

  Set fso = CreateObject("Scripting.FileSystemObject")

  IF (fso.FileExists("\\yourserver\path\file.dat")) THEN

  Main = DTSStepScriptResult_ExecuteTask

  ELSE

  Main = DTSStepScriptResult_DontExecuteTask

  END IF

  End Function

  Second, if you use a "on completion" vs. "on success" connector between steps, the VBScript check is not necessary. I have multiple packages which will import a series of files and if they don't exist, the package will just continue on if I use the "on completion" connector. In my case I don't really care if it exists, so this works.

  If you really need to know if the file exists, then the above script should help.

  David

  David

  @SQLTentmaker

  “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

• Jason Delaune

  SSCrazy

  Points: 2649

  More actions

  November 12, 2001 at 3:50 pm

  #420037

  Hello everyone,

  Well, stupid errors sometimes get you into trouble and this was one case where it did. When I created the file, I added the ".txt" extension, but my machine had hidden known file types. So the file came out as "Test.txt.txt". I was only checking for "Test.txt", and that's why it failed everytime. DUH !!! Thanks for your help!

  Jason