Knowledge.ToString()

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

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

Comments

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

  1. james Avatar
    james

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

  2. steve Avatar
    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

Leave a Reply

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