通过命令从 SQL Server 下载 FTP 服务器上的文件

引言

在常规编码过程中，我们通常使用 C# 代码或其他代码从 FTP 服务器下载文件，这可能需要花费大量时间编写代码和下载。相反，我们可以直接从 SQL 下载文件。

背景

为此，你只需要正常的 SQL 语法和一些命令行语法。

使用代码

要从 FTP 服务器下载文件，你可以使用 SQL Server 和 FTP 的 'Mput' 命令，这比编写代码少得多，并且下载时间会稍快一些。反向过程也是可行的，你可以使用 'MPut' 方法将文件上传到 FTP 服务器。

你可以使用以下脚本从 FTP 服务器下载文件。只需传递你实际的属性，即可完成，你的文件将被下载。

MGet 命令

-- FTP_MGET.sql (Author Saddamhusen Uadanwala)  
-- Transfer all files from an FTP server to local Direcoty using MGET command.  
   
DECLARE @FTPServer varchar(128)  
DECLARE @FTPUserName varchar(128)  
DECLARE @FTPPassword varchar(128)  
DECLARE @SourcePath varchar(128)  
DECLARE @SourceFiles varchar(128)  
DECLARE @DestinationPath varchar(128)  
DECLARE @FTPMode varchar(10)  
   
-- Attributes  
SET @FTPServer = 'ftpserver'  
SET @FTPUserName = 'username'  
SET @FTPPassword = 'password'  
SET @SourcePath = '' -- Folder path/Blank for root directory.  
SET @SourceFiles = '*.csv'  
SET @DestinationPath = 'D:\Husen\Download' -- Destination path.  
SET @FTPMode = 'binary' -- binary, ascii or blank for default mode.  
   
DECLARE @Command varchar(1000)  
DECLARE @workfile varchar(128)  
DECLARE @nowstr varchar(25)  
   
-- %TEMP% environment variable.  
DECLARE @tempdir varchar(128)  
CREATE TABLE #tempvartable(info VARCHAR(1000))  
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'  
SET @tempdir = (SELECT top 1 info FROM #tempvartable)  
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'  
DROP TABLE #tempvartable  
   
-- Generate @workfile  
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')  
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'  
   
-- special chars for echo commands.  
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')  
select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')  
select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')  
select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')  
IF RIGHT(@DestinationPath, 1) = '\' SET @DestinationPath = LEFT(@DestinationPath, LEN(@DestinationPath)-1)  
   
-- Build the FTP script file.  
select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
IF LEN(@FTPMode) > 0  
BEGIN  
    select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile  
    EXEC master..xp_cmdshell @Command  
END  
select @Command = 'echo ' + 'lcd ' + @DestinationPath + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
IF LEN(@SourcePath) > 0  
BEGIN  
    select @Command = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile  
    EXEC master..xp_cmdshell @Command  
END  
select @Command = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
   
-- Execute the FTP command via above generated script file.  
select @Command = 'ftp -s:' + @tempdir + @workfile  
create table #a (id int identity(1,1), s varchar(1000))  
print @Command  
insert #a  
EXEC master..xp_cmdshell @Command  
select id, ouputtmp = s from #a  
   
-- drop table.  
drop table #a  
select @Command = 'del ' + @tempdir + @workfile  
print @Command  
EXEC master..xp_cmdshell @Command

你可以使用以下脚本将文件从本地目录上传到 FTP 服务器。

MPut 命令

-- FTP_MPUT.sql (Author Saddamhusen Uadanwala)  
-- Transfer all files from an FTP server Direcoty using MPut command.  
  
DECLARE @FTPServer varchar(128)  
DECLARE @FTPUserName varchar(128)  
DECLARE @FTPPassword varchar(128)  
DECLARE @SourcePath varchar(128)  
DECLARE @SourceFiles varchar(128)  
DECLARE @DestinationPath varchar(128)  
DECLARE @FTPMode varchar(10)  
   
-- Attributes  
SET @FTPServer = 'ftpserver'  
SET @FTPUserName = 'username'  
SET @FTPPassword = 'password'  
SET @SourcePath = 'D:\Husen\Upload' -- Destination path.  
SET @SourceFiles = '*.csv'  
SET @DestinationPath = '' -- Folder path/Blank for root directory.  
SET @FTPMode = 'binary' --  binary, ascii or blank for default mode.  
   
DECLARE @Command varchar(1000)  
DECLARE @workfile varchar(128)  
DECLARE @nowstr varchar(25)  
   
-- %TEMP% environment variable.  
DECLARE @tempdir varchar(128)  
CREATE TABLE #tempvartable(info VARCHAR(1000))  
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'  
SET @tempdir = (SELECT top 1 info FROM #tempvartable)  
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'  
DROP TABLE #tempvartable  
   
-- Generate @workfile  
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')  
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'  
   
-- Deal with special chars for echo commands.  
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')  
select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')  
select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')  
select @DestinationPath = replace(replace(replace(@DestinationPath, '|', '^|'),'<','^<'),'>','^>')  
IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'  
   
-- Build the FTP script file.  
select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
IF LEN(@FTPMode) > 0  
BEGIN  
    select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile  
    EXEC master..xp_cmdshell @Command  
END  
IF LEN(@DestinationPath) > 0  
BEGIN  
    select @Command = 'echo ' + 'cd ' + @DestinationPath + ' >> ' + @tempdir + @workfile  
    EXEC master..xp_cmdshell @Command  
END  
select @Command = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command  
   
-- Execute the FTP command via above generated script file.  
select @Command = 'ftp -s:' + @tempdir + @workfile  
create table #a (id int identity(1,1), s varchar(1000))  
insert #a  
EXEC master..xp_cmdshell @Command  
select id, ouputtmp = s from #a  
   
-- drop table.  
drop table #a  
select @Command = 'del ' + @tempdir + @workfile  
EXEC master..xp_cmdshell @Command 

这里，我们使用你的参数创建了一个包含命令列表的简单文本文件并执行了它。请注意，连接性和目标路径必须存在。

关注点

现在，请参考此链接并自行尝试其他命令 http://www.nsftools.com/tips/MSFTP.htm。