Solved

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

Posted on 2016-10-19
10
28 Views
Last Modified: 2016-11-24
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
0
Comment
Question by:Jim Horn
  • 5
  • 3
  • 2
10 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41850332
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.
0
 
LVL 4

Expert Comment

by:Kimberley from Paessler
ID: 41851724
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.
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41852111
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.
1
 
LVL 4

Expert Comment

by:Kimberley from Paessler
ID: 41853325
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.
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41874082
Are you still looking for a solution to this, Jim?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points (awarded by participants)
ID: 41874107
I was able to answer my question using Crush FTP's folder events and jobs, which requires an enterprise license.   I do appreciate Dustin's PowerShell solution, that will give me a reason to brush up on my PowerShell skills.

The client was using CrushFTP already but with a license less than Enterprise, so this task will give them reason to get one.

Also I advocated for the 'Just schedule the SQL Agent job to run every x minutes' approach, but that was turned down as files will be landing every 3-5 minutes, and management wanted to be able to tell their client that files would be loaded <finger quotes> automatically </finger quotes>.

Preferences > Folder Monitor, can point to a specific folder and have it run a job when files are added
Chrome-Crush-UI-Folder-Monitor-run-j.png
Jobs > Can create a job that runs an executable file
Chrome-Crush-UI-Job-Run_Job_TravcomN.png
Then create a .bat file that fires the SQL Agent job (somewhat mocked up)
ECHO Executing job

ECHO.

CD C:\Program Files\Microsoft SQL Server\100\Tools\Binn

osql -S 'my.server.name' -E -Q"exec msdb.dbo.sp_start_job 'my_sql_agent_job'"


ECHO Job execution completed

pause

CLS

EXIT

Open in new window


Right now when the .bat file executes we're getting the below error message when the .bat file executes, likely due to SQL Server permission, and we're working on that..
[SQL Server Native Client 10.0]Named Pipes Provider:  
Could not open a connection to SQL Server [53].

Open in new window

0
 
LVL 65

Author Comment

by:Jim Horn
ID: 41874129
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.
1
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41874146
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.
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 41874156
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."
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41898862
Found solution himself with software.
0

Join & Write a Comment

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now