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

Category: SSIS
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

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.

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.

Download Sample

Share

2 comments

Your email address will not be published.

  1. james says:

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

  2. steve says:

    /*
    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