SQL Server to check for files in directory

Question

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?

I am looking at options... if i were to use C#, how would you approach this? — Peter Sun
– Peter Sun, Commented May 13, 2018 at 19:06
this stackoverflow.com/questions/38960/… or this stackoverflow.com/questions/2998384/… and then this social.technet.microsoft.com/wiki/contents/articles/… 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
– Aaron Bertrand, Commented May 13, 2018 at 20:33

jspcal · Accepted Answer · 2018-05-13 15:31:06Z

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.

edited May 13, 2018 at 15:31

answered May 13, 2018 at 14:42

jspcal

52k7 gold badges76 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Sun Over a year ago

i guess i should be more specific.. is there a way to automatically set sql server to check a directory and run a stored procedure?

jspcal Over a year ago

Outside of SSIS, you can schedule a script to do that.

yob · Accepted Answer · 2023-06-25 14:39:48Z

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/

answered Jun 25, 2023 at 14:39

yob

5284 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Server to check for files in directory

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related