Sleep if file not available. Load if it is.

One of my old friends, asked me couple of days back how to design a process that will check for file existence in a folder. On finding the file the process should load the file else should sleep for 15 mins and then check for file existence. This process can be handled using advanced T-SQL batch that contains "WAITFOR" and "BCP" like below:

-- Code Starts here
DECLARE @File VARCHAR(500), @Sleeps INT, @FileAvailable INT, @Iteration INT

SET @FileAvailable = 0
SET @Iteration = 0
SET @Sleeps = 4
SET @File = 'C:\Data_DEPT.dat'

-- Check for File Existence
EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT

-- If the file is not available, then the process will wait (sleep) for specified time frame.
-- Here I am placing just 15 seconds time frame, but can you can specify anything.
WHILE @FileAvailable = 0 AND @Iteration < @Sleeps
  BEGIN
    SET @Iteration = @Iteration + 1
    WAITFOR DELAY '00:00:15'
    EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT
  END 

-- If the file is available, then load the file into DEPT2 table.
IF @FileAvailable = 1
BEGIN
DECLARE @BCP VARCHAR(500)
SET @BCP = 'bcp AdventureWorks2012.dbo.DEPT2 IN ' + @File + ' -f C:\Metadata_DEPT.fmt -S HPS\SQL -T'
EXEC MASTER..xp_cmdshell @BCP
END
-- Code Ends here

Execution of above code will result this:

Output of the data in table after file load:

Conclusion: I used two master stored procedures here "xp_FileExist" and "xp_cmdshell". The metadata file (format file) of the FlatFile is assumed to be predefined here. So similar structured files carrying different data can be processed. Hope this helps! Happy TSQLing ;)