DTS SQL Statement / Stored procedure / Function to check for existence of file.

By | October 30, 2006
CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255))
RETURNS bit AS
BEGIN
DECLARE @FS int
DECLARE @FileID int
DECLARE @OLEResult int
DECLARE@Exists bit

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
RETURN 0
END

EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName

IF @OLEResult <> 0
BEGIN
return 0
END
EXECUTE @OLEResult = sp_OADestroy @FS

return @Exists
END

Important things to remember.

  1. Existence of the file will be checked on the server in which it is created. If you are running this stored procedure on remote machine, it will check for file on remote machine and not on local machine.
  2. To call a function, you should write
    if dbo.fn_FileExists ('Filename.txt') = 1
    print 'exist'

    Here “dbo.” must be appended to call the function, otherwise it will give error.

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

2 thoughts on “DTS SQL Statement / Stored procedure / Function to check for existence of file.

  1. steve

    /*
    to avoid error:
    SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’
    */
    — run this:
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO

    sp_configure ‘Ole Automation Procedures’, 1;
    GO
    RECONFIGURE;
    GO

  2. james

    Hello, Will it be possible to perfprm DTS by using T-sql statement?
    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *