Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Download file(s) from FTP Server using Command through SQL Server

Rate me:
Please Sign up or sign in to vote.
3.86/5 (6 votes)
14 Feb 2017CPOL 24.3K   8   5
Download files using T-SQL server which is very much faster then any other option.

Introduction

In regular life of coding we are downloading files from FTP server using c# code or any other code it may take much time to code as well as more time to download. Insted of that we can download a file from directly sql.

Background

For this you just need normal SQL syntax and some command line syntax. 

Using the code

To download files from FTP server you can use SQL server with 'Mput' command of FTP which is much lesser to code and will take a bit of time to download. Inverse process is also possible as you can use 'MPut' method to upload a file to FTP server.

For this you can use following script for download file(s) from FTP Server. Just pass your actual attributes and you have done, your files are downloaded. 

MGet Command

SQL
-- 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

You can use following script to Upload file(s) from local directory to FTP Server.

MPut Command

SQL
-- 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 

Here, we have made a simple text file with a list of commands using your parameters and executed it. Gentle reminder, commectivity and destination path should be there.

Points of Interest

Now, refer this link and try other commands http://www.nsftools.com/tips/MSFTP.htm by yourself.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) MSP
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questioncopy and delete file from ftp Pin
Member 1347781231-May-20 0:48
Member 1347781231-May-20 0:48 
QuestionAUTH SSL/TLS required prior to authentication login failed Pin
Member 1449622912-Jun-19 3:04
Member 1449622912-Jun-19 3:04 
QuestionFTP with port number Pin
Member 80487924-May-18 21:36
Member 80487924-May-18 21:36 
QuestionInteresting Idea Pin
masteripper14-Feb-17 19:03
masteripper14-Feb-17 19:03 
QuestionSnippets problem Pin
Nelek14-Feb-17 2:23
protectorNelek14-Feb-17 2:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.