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,563
  • 4
  • 21
  • 45
  • 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

1 Answers1

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
  • 49,231
  • 7
  • 69
  • 74