-1

I will have daily 3 files coming into my C: drive with timestamps in YYMMDDhhmm format. for ex. TotalSale1611160037 means 2016-11-16 12:37 am

TotalSale1611160037 
RegionSale1611160037
Statesale1611160037

I have scheduled a job in SQL Server Agent to load these 3 files into SQL Server 2014.

My requirement is that before starting the loading job, I need to check if the 3 files are present on the C: drive. The job should run only if the 3 files are present on the C: drive.

If any of these files are missing on the C: drive, the load job should not run and send a database email showing which files are missing.

The first part of the files (TotalSale,RegionSale,Statesale) will be same every day; only the timestamp (1611160037) will change. After the load process I will move those files into another folder.

Please help me. I don't know how to check files exist using wild card characters in SQL Server.

Thank you for your time and help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Please
  • 41
  • 1
  • 7
  • There are ways to do this in SQL, that includes xp_cmdshell, but it is a security issue. I suggest you put this in the application layer, that is use .net or java and then trigger the sql server SP from the app layer instead of coding all this in SQL Server – Surendra Nov 16 '16 at 16:40
  • Possible duplicate of [Check for file exists or not in sql server?](http://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server) – Doliveras Nov 16 '16 at 16:46
  • typically you would use an SSIS Script task and System.IO.File.Exists() function and/or system.io.directoryinfo.getfiles and loop through to see if the files you want exists. – Matt Nov 16 '16 at 19:28

1 Answers1

0

Create a Execute sql task and use the following t-sql 

Declare @AllFilesExist  INT = 0
       ,@BasePath VARCHAR(100) = 'D:\backups'

Declare @Files TABLE ([FileName] VARCHAR(100), Depth INT, [File] INT) 

INSERT INTO @Files
EXEC master.sys.xp_dirtree @BasePath,1,1;


Select TOP 1 @AllFilesExist = 1
FROM @Files
WHERE EXISTS ( SELECT 1 
               FROM @Files
               WHERE [FileName] LIKE '%TotalSale%')
AND EXISTS (  SELECT 1 
               FROM @Files
               WHERE [FileName] LIKE '%RegionSale%')
AND EXISTS (  SELECT 1 
               FROM @Files
               WHERE [FileName] LIKE '%Statesale%')

-- Now use @AllFilesExist Variable to decide the execution flow in your SSIS package
M.Ali
  • 67,945
  • 13
  • 101
  • 127