Files in SQL stored procedure

Question

Hello all,

I'm currently tasked with reading some data that stored in a flat file into my database and run reports against it. The one problem I'm running into is checking to see if a file actually exists. Is there a simple function to check if the file exists?

Thanks!

Answer 1

Just googling I found this at SQL DBA and this at MS SQL tips.

Answer 2

I believe you can do something like this:

DECLARE @Path varchar(128) ,
 @FileName varchar(128)
 SET @Path = 'C:\'
 SET @FileName = 'FILE_NAME.EXT'

DECLARE @objFSys int
DECLARE @i int
DECLARE @File varchar(1000)

 SET @File = @Path + @FileName
 EXEC sp_OACreate 'Scripting.FileSystemObject', @objFSys out
 EXEC sp_OAMethod @objFSys, 'FileExists', @i out, @File
 IF @i = 1
  PRINT 'file exists'
 ELSE
  PRINT 'file does not exists'
 EXEC sp_OADestroy @objFSys 

This article goes over this method and a couple others.

Answer 3

You are doing ETL in a stored procedure?!! I don't think you should, just because you can.

I recommend you use use SSIS for this. Doing ETL in Stored Proc or TSQL is not a recommended practice, in fact, it is frequently used as an example of what not to do.