Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

Is there such an animal as 'SFTP Folder event that fires when a file is created?'

Hi All

(Warning:  SQL Server expert, everything else related to this question N00b.)

I've been tasked with finding out if a SFTP folder (CrushFTP) can have an event that fires when a file has been added to it, and if that event can execute a SQL Server Agent job or any custom script?

For some reason the client is opposed to a SQL Agent job firing every 10 minutes to detect if the file exists, and if it does not then to exit the job.

Thanks.
Jim
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

You can create a file system watcher event to detect the creation and then fire the event.   You can compile it inside a loop as a service with PowerGUI so it continually runs, monitor the service for any issues, etc.
Does the client have any kind of monitoring tool running (eg. PRTG, nagios, Solarwinds)?  If so, that tool should be able to monitor the folder to see if a file has appeared, to check the file timestamp, etc.  And then it could report on any changes it finds, typically by email or SMS.

It would be overkill to set up a generic monitoring tool just for this, but if they already have a tool running, maybe they can add this to their existing tool.
I don't believe the intent is to alert when the file appears, the intent is most likely to do something with that file that involves the database.  From Powershell triggering or executing a sqlcmd is extremely simple.

When I mentioned monitoring the service, I mean you can set up your existing monitoring tools to watch the service; meaning you get an alert if the service goes down for any reason.
I agree:  he could use existing monitoring tools to watch the service and alert if the service goes down.  In addition, depending on what tool he's using, he might also be able to watch the file and then react automatically.   With PRTG, for example, he could detect an event happening in the folder, and then use a "script notification" to trigger a script with his powershell or SQL commands in it.  The script notification is just an additional type of alert -- instead of sending an email, you run a script.

Unfortunately, if the client is opposed to using an SQL Agent job, he probably doesn't have a monitoring tool running either.  Because, fundamentally, this job would be doing essentially the same thing as a monitoring tool would do:  periodically check if the file is there and then run some commands.
Are you still looking for a solution to this, Jim?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

btw Reading your profile I see you're in Eagan.  I'm in Eden Prairie doing SQL and SSIS development, and I've had freelance contract gigs in your neck of the woods at CHS and Sun Country.

Congratulations on landing the Vikings at the old Northwest complex.  EP clearly dropped the ball on that one.
I used to work in EP off Flying Cloud Dr (and my current company's datacenter is at the Level3 colo over by Shady Oak/Hwy 62, so I'm still out that way often).  Traffic's a little nicer for me coming this way.

EP has a better food selection though; but as you mentioned maybe moving the Vikings here will inspire more restaurants to come in.
Probably, and a bunch of players will eventually buy houses in Eagan when the facility moves, and there will be an increase in upscale apartments as the Viking purchase a block for less than permanent staff and players than come and go.

Rick Spielman gave us a tour of the Vikings complex last year, and he showed us the board room where they run the draft.  I asked him if he saw the movie Draft Day and what he thought of Kevin Costner's performance as GM, and his witty comeback was "Yeah, it's pretty close to reality, although I don't think I'm getting my salary cap guy knocked up anytime soon."
Found solution himself with software.