asked
Sql server agent to look for files
Is there a way to create a sql server agent job where the program will keep looking for a directory to find certain files and if the files are found then move to the next job? I guess I’m looking something similar to file system watcher in C#. So every day I receive files at a different time via FTP and it’s not at any specific time. Once I receive the all the required files only then I can start the update process. I’m trying to automate the process by creating a job that will look for files and if the job becomes successful then start the update process. Thanks.
A method I've used is to create an SSIS package for the purpose and trigger that from SQL Agent. If, for example, you wanted to import a file once it was creates, you could use the WMI watcher task to poll for the file being created and then move on to a data flow task to import it. If you're waiting for several files, you'll probably need several watcher tasks, but it should be possible
I looked at several ssis wmi tutorials but still confused. I want to look for a particular set of files (around 20 out of 100 files that comes in each day) and once I have all the file then start an agent job. I want the program to continuously look for those files in the folder and once it finds the necessary files then stop the package. Most of the tutorials I found talks about continuously watching a directory and once file arrive then process them. But I want to look for particular files and also want to stop the package if I find the necessary files. Is that possible with WMI? Thanks.
I think this is where in the SSIS package you would need a WMI Event Watcher task for each file. When all of them see the file arrive that package could complete and move onto the next step in the Agent job or move on to subsequent package steps to process the files.
14 People are following this question.
