Every weekday we receive a file which under normal circumstances gets processed by a SQL Job that runs on a schedule at 7 AM. It's worked this way for years. This file is produced by a partner, who uploads it to a secure FTP site.
However, for the last several weeks the file has either been late or never arrived at all. This is an issue, and the partner is doing what they can to resolve it. However, in the meantime I've been asked to consider other ways of automating the process. Having a way that SQL Server would "known" when the file arrives, so it can process it. The file, when it arrives, is always named the same thing. And the SQL Job that processes it, will rename the file and move it to another folder.
I've thought that if this were in Azure/AWS/GCP then there would be a way of detecting when a file arrives, but this is on-prem behind our firewall, so any cloud-based solution wouldn't have access to the folder.
So, does SQL 2008 R2 have anything similar that can be used on-prem?
Kindest Regards, Rod Connect with me on LinkedIn.
I don't know of a windows-driven "interrupt" that would do it (and, if someone does, please fire a flare 'cuz I'l love to know it!) but, if if there was one, it wouldn't give you an alert when one didn't show up at all.
In it's simplest form, you could fire a job every 5 or 10 minutes to look for the file using xp_DirTree starting at some earliest assigned time. Once it's found, it could fire the import job and that job could set a flag in a process control table so that the detector job would exit super early.
The detector job could also be setup to have a "latest expected/required time" where if it didn't detect and process the file for the given day by a certain time, it could send out an email as an alert to multiple parties.
Some will suggest the use of the Windows Scheduler for the "detection". Personally, I hate the Windows Scheduler.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2023 at 5:25 pm
It's pretty straight forward to do that in powershell as the first step in the script and periodically check for the file then succeed the step when it shows up or fail it at some point. Especially if you just need it for the one job and there's no need to make something more generic that can handle inputs parameters etc....
February 10, 2023 at 11:32 pm
There are some 3rd party 'file watcher' components for SSIS. Not worth it on many levels. https://andyleonard.blog/2017/11/ssis-design-patterns-sniffer-file-watcher/
Like Jeff says poll as frequently as needed, log file found situations, short circuit the job based on logging and provide email notifications.
February 13, 2023 at 1:22 am
You can just schedule the job to run every 5 minutes starting at 7 am until a time when you say the file has failed to arrive.
February 13, 2023 at 10:22 am
Polling is probably the best solution but you might also be able to Powershell (.Net) to start a SQL process with event tracing:
https://mcpmag.com/articles/2019/05/01/monitor-windows-folder-for-new-files.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply