0

I was wondering if it is possible for SQL Server to check a directory for files and run a stored procedure. I did some research and found this, but I am wondering if there is a way to do what I want WITHOUT SSIS.

EDIT: After reading my post, I realized I should have been more specific. Is there a way to AUTOMATICALLY or set SQL Server to check for files in a directory and run a stored procedure?

Peter Sun
  • 1,675
  • 4
  • 27
  • 50
  • Why not use Powershell or C# to do this? – Aaron Bertrand May 13 '18 at 15:09
  • I am looking at options... if i were to use C#, how would you approach this? – Peter Sun May 13 '18 at 19:06
  • this https://stackoverflow.com/questions/38960/how-to-find-out-if-a-file-exists-in-c-sharp-net or this https://stackoverflow.com/questions/2998384/what-is-the-easiest-way-to-loop-through-a-folder-of-files-in-c and then this https://social.technet.microsoft.com/wiki/contents/articles/37252.c-timer-schedule-a-task.aspx It's not really SQL Server's job to do what you're asking, but you can certainly call stored procedures from C# – Aaron Bertrand May 13 '18 at 20:33

2 Answers2

1

You can use xp_cmdshell to run file related commands. To get a directly listing:

exec xp_cmdshell 'dir *.csv';

You can also use bulk insert to load a file from disk into a table and take actions based on the loaded contents.

Normally you'd use the File Watcher Task with SSIS. But you can also use SQL Server Agent to schedule a task for periodic execution, schedule a task with Windows Task Scheduler, or configure a stored procedure to runs at startup with sp_procoption that pauses (using waitfor) between processing times.

jspcal
  • 50,847
  • 7
  • 72
  • 76
0

for SQL2017 + :

sys.dm_os_enumerate_filesystem('C:\', '*')

OR

DECLARE @LatestBackupFile VARCHAR(255)
    SELECT TOP 1 @LatestBackupFile = fl.file_or_directory_name
    FROM sys.dm_os_enumerate_filesystem(@BackupFolder, @FullBackupFilePattern) fl
    WHERE creation_time >= @LastRestoreEnd 
    ORDER BY creation_time DESC

where @BackupFolder - folder, and @FullBackupFilePattern = 'WideWorldImportersbackups*.bak'

see more at https://support.microsoft.com/en-us/topic/kb4046638-add-the-ability-to-disable-or-enable-a-few-new-dmvs-and-dmfs-introduced-in-sql-server-2017-05d709c5-4522-3168-2cb1-f755fddff9e7

and at https://www.brentozar.com/archive/2017/07/sql-server-2017-less-xp_cmdshell/

yob
  • 528
  • 4
  • 9