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.