SQL job and VBS woes

SQL job and VBS woes

Post by Michael Slatof » Wed, 18 Apr 2001 02:05:13



I've got a SQL job that has four steps. In step one I want to check to see
if a file exists. If it doesn't exist, I want the job to stop and notify me
via email. Here is the VBS that I'm using.

-------------------------
Function Main()
 DIM fso
 SET fso = CreateObject("scripting.FileSystemObject")
 IF
fso.FileExists("\\coghill\e$\Extracts\Data\Intranet\isutext\isumerge.txt")
THEN
  Main = DTSTaskExecResult_Success
 ELSE
  Main = DTSTaskExecResult_Failure
 END IF
 SET fso = nothing
END Function
-------------------------

If the file does exists, my job completes with out any problems. If the file
doesn't exist, the job moves to step two where I try to import the text file
into a table via a DTS package. Since the file doesn't exist, the job bombs.

Any help would be greatly appreciated.

 
 
 

SQL job and VBS woes

Post by Jim » Wed, 18 Apr 2001 05:54:13


No need to use vbs, you can do it from within tsql step using xp_fileexists.
Example:

Create Table #tblFileExists  (
  DoesExist  smallint,
  FileInDir  smallint,
  DirExists  smallint)
go


 Insert into #tblFileExists

 If Exists (Select * from #tblFileExists where DoesExist = 1)
  Begin
                      print 'file exists'
                      -- your logic
                end
drop table #tblFileExists


Quote:> I've got a SQL job that has four steps. In step one I want to check to see
> if a file exists. If it doesn't exist, I want the job to stop and notify
me
> via email. Here is the VBS that I'm using.

> -------------------------
> Function Main()
>  DIM fso
>  SET fso = CreateObject("scripting.FileSystemObject")
>  IF
> fso.FileExists("\\coghill\e$\Extracts\Data\Intranet\isutext\isumerge.txt")
> THEN
>   Main = DTSTaskExecResult_Success
>  ELSE
>   Main = DTSTaskExecResult_Failure
>  END IF
>  SET fso = nothing
> END Function
> -------------------------

> If the file does exists, my job completes with out any problems. If the
file
> doesn't exist, the job moves to step two where I try to import the text
file
> into a table via a DTS package. Since the file doesn't exist, the job
bombs.

> Any help would be greatly appreciated.