File Monitoring Using T-SQL

Ever wanted to check for the files and then take some action? - this is very common situation where some team will be providing a data feed or extract (as some people call it). Which are usually in flat file format. Before consuming those files you may want to check if you have received all the files are not and then start the consumption.

But in my case what happened was the SSIS flow was written in such way, it will start consuming the files and then at the end when it finds that one of the file is missing it used to rollback the operation which used to take 30-40 minutes to realize. So I put a step before that SSIS execution step to check for file and following is my implementation

-- Check if my files are available

DECLARE @return_status INT

DECLARE @myFileMask VARCHAR(50)

SET @myFileMask = LEFT(REPLACE(CONVERT(varchar, GETDATE(), 120), '-', ''), 8) + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 114), ':', ''), 2) + '*.*'

EXEC @return_status = dbo.prc_CheckForFiles

@FileMask = @myFileMask,

@FullPath = 'E:\FlatFilesDump\',

@FileCount = 7

IF @return_status = 0

BEGIN

RAISERROR ('My files are not available yet. Please check !', 16, 1)

END

--consume files if all are available

IF @return_status = 1

BEGIN

PRINT 'Files are available!'

EXEC dbo.usp_Test -- this is just a test SP

END

---===================================================================================

Here is the script for prc_CheckForFiles

---===================================================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

/**

Author: Girish

Purpose: To validate if the set of files exist or not (YES/NO)

Usage: Its takes 3 inputs, root path, file mask and count

**/

CREATE PROC [dbo].[prc_CheckForFiles]

(

@FileMask VARCHAR(50), -- search mask like ab*_.txt etc

@FullPath VARCHAR(1000), -- search path like "C:\DumpFilePath\"... UNC paths are NOT ALLOWED

@FileCount INT -- integer to indicate the number of files to be looked for, based on which success or failure is indicated

)

AS

SET NOCOUNT ON

--PRINT @FileMask

--PRINT @FullPath

--PRINT @FileCount

CREATE TABLE #Tmp(

cmdOutput VARCHAR(500)

)

DECLARE @DateStamp VARCHAR(30)

, @CMD VARCHAR(1000)

, @myRowCount INT

SET @CMD = 'DIR '+@FullPath+''+@FileMask+' /B'

--PRINT @CMD

INSERT INTO #Tmp EXEC master.dbo.xp_cmdshell @CMD

SET @myRowCount = @@ROWCOUNT

IF @myRowCount >= @FileCount

RETURN 1 -- expected number of files were found

IF @myRowCount < @FileCount

RETURN 0 -- expected number of files NOT found

DROP TABLE #Tmp

--end

GO

Please note, since this depends on xp_cmdshell and DOS – it might limit you ability to implement in your specific environments.