executing FTP commands

Question

What is the best method for executing FTP commands from a SQL Server stored procedure? we currently use something like this:

EXEC master..xp_cmdshell 'ftp -n -s:d:\ftp\ftpscript.xmt 172.1.1.1'
The problem is that the command seems to succeed even if the FTP ended in error. Also, the use of xp_cmdshell requires special permissions and may leave room for security issues.

Answer 1

Basically: None, as when implemented as synchronous operation it may last in a long running transaction which depends on external systems. This is normally an unwanted situation.

Otherwise when it is really needed, I would use .NET CLR integeration and use a FTP procedure written in C#/VB.NET. But this also requires enhanced permissions. E.g. How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration and use an FtpWebRequest.

Answer 2

I agree with Stefan that it's probably better to perform the FTP using an asynchronous technique.  Instead of xp_cmdshell, you could launch an SSIS package asynchronously.  This can be done with sp_start_job, the Service Broker External Activator or SSIS catalog procs (SQL Server 2012).

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Hi Money,

If you're running SQL 2005 you could do this in a CLR integration assembly and use the FTP classes in the System.Net namespace to build a simple FTP client.
 
You'd benefit from being able to trap and handle exceptions and reduce the security risk of having to use xp_cmdshell.

---

Iric Wen
TechNet Community Support