File Validation in SQL Server (xp_fileexist)

With the help of xp_fileexist SP, we will check the file in the folder exists or not.

Let’s see the example.

   

 

In this folder, I have a file.

Using the below script we will check it.

DECLARE @Is_File_Exists INT,                                 @File_name varchar(100); SET @File_name ='J:\SSIS1\Source\Product\Product_File.txt' EXEC Master.dbo.xp_fileexist @File_name, @Is_File_Exists OUT IF @Is_File_Exists = 1 BEGIN                 PRINT 'File '+ @File_name + ' Exists...' END ELSE BEGIN                 PRINT 'File  '+@File_name+ '  Does not Exists...' END

Executing this script

    

Now, check with a not existing file.

Product_File_1.txt does not exist in the above folder.

DECLARE @Is_File_Exists INT,                                 @File_name varchar(100); SET @File_name ='J:\SSIS1\Source\Product\Product_File_1.txt' EXEC Master.dbo.xp_fileexist @File_name, @Is_File_Exists OUT IF @Is_File_Exists = 1 BEGIN                 PRINT 'File '+ @File_name + ' Exists...' END ELSE BEGIN                 PRINT 'File  '+@File_name+ '  Does not Exists...' END

Running now.