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.
