FTP From File Share (or SSRS) via SQL

  • We generate a report file to a file share automatically via an SSRS Report Manager subscription, the next stage is to FTP this file somewhere. At the moment this has always been a manual task as it's not something we need to do very often, recently we've run into a project where a weekly upload via FTP would be handy, clearly manual is a bit of a pain with this sort of frequency!

    Something like this: http://beyondrelational.com/modules/1/justlearned/388/tips/8824/script-to-upload-files-via-ftp-in-sql-server.aspx looks useful - it can just take the file we create above and dump it on the ftp.

    I'm keen to have something with some form of error catching and alerting/logging though, so if it were to log the FTP transfers that happened, send an email if there was a failure, or even an email on success at first perhaps, this would be really beneficial.

    It seems the above is a better method of doing things compared to oldskool batch files.

    Is there a more robust method, am I missing something obvious within SSRS or some form of plugin or something?

    Ideas, feedback and comments most welcome 🙂 Thanks.

  • A most robust and reliable method to do this is to have a separate process implemented as a service or a scheduled task that pushes the files to an FTP or SFTP server. This process would get its requests via a service broker messages (queue).

    Clients send requests via a message to the service. The message would contain the path to the file and possible login credentials to the server. The service can retry the transaction X number of times in case the server is down or other network issue prevents it from happening immediately.

    To try to implement this in real time via a CLR procedure or some klugy batch file using xp_cmdshell is not a good idea for obvious reasons.

    The probability of survival is inversely proportional to the angle of arrival.

  • Thanks for the response, I'm not sure I fully follow, do you have any links to articles explaining more or something?

    Thanks.

  • Not off the top of my head. I've used service broker as a TSQL interface for several back-end web services with excellent results.

    If you are not sure how to do this use Google and you're bound tot find some examples of this sort of implementation. Msdn has some fairly good information on using service broker. Having said that, since you've never done this before you might just want to tap into someone that knows .NET programming to build this for you.

    Just to add an additional thought: you could do this by writing the FTP requests to a table instead of a service broker queue, than have the service poll the table periodically. The downside to this is that you have to poll the table so you lose the immediacy... and results feedback to the client. Also, this approach, while seeming easier, takes a bit more work on the coding side to make it fully multi-theaded compared to the service broker approach.

    The probability of survival is inversely proportional to the angle of arrival.

  • I'll hassle some of our in-house developers, I've never touched dot net in my life (besides reading the odd bit of it here and there to try and see what it's doing). See if any of them have done anything like this before.

    Cheers.

  • You could do this by writing the FTP requests to a table instead of a service broker queue, than have the service poll the table periodically. The downside to this is that you have to poll the table so you lose the immediacy... and results feedback to the client. Also, this approach, while seeming easier, takes a bit more work on the coding side to make it fully multi-theaded compared to the service broker approach.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply