Post
Ask Question

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

In regular life of coding we are downloading data/files from server using c# code or any other code it may take much time to code as well as more time to download. This same thing you can be done using 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.

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

MGet Command
  1. -- FTP_MGET.sql (Author Saddamhusen Uadanwala)  
  2. -- Transfer all files from an FTP server Direcoty using MGET command.  
  3.    
  4. DECLARE @FTPServer varchar(128)  
  5. DECLARE @FTPUserName varchar(128)  
  6. DECLARE @FTPPassword varchar(128)  
  7. DECLARE @SourcePath varchar(128)  
  8. DECLARE @SourceFiles varchar(128)  
  9. DECLARE @DestinationPath varchar(128)  
  10. DECLARE @FTPMode varchar(10)  
  11.    
  12. -- Attributes  
  13. SET @FTPServer = 'ftpserver'  
  14. SET @FTPUserName = 'username'  
  15. SET @FTPPassword = 'password'  
  16. SET @SourcePath = '' -- Folder path/Blank for root directory.  
  17. SET @SourceFiles = '*.csv'  
  18. SET @DestinationPath = 'D:\Husen\Download' -- Destination path.  
  19. SET @FTPMode = 'binary' -- binary, ascii or blank for default mode.  
  20.    
  21. DECLARE @Command varchar(1000)  
  22. DECLARE @workfile varchar(128)  
  23. DECLARE @nowstr varchar(25)  
  24.    
  25. -- %TEMP% environment variable.  
  26. DECLARE @tempdir varchar(128)  
  27. CREATE TABLE #tempvartable(info VARCHAR(1000))  
  28. INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'  
  29. SET @tempdir = (SELECT top 1 info FROM #tempvartable)  
  30. IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'  
  31. DROP TABLE #tempvartable  
  32.    
  33. -- Generate @workfile  
  34. SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')  
  35. SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'  
  36.    
  37. -- special chars for echo commands.  
  38. select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')  
  39. select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')  
  40. select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')  
  41. select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')  
  42. IF RIGHT(@DestinationPath, 1) = '\' SET @DestinationPath = LEFT(@DestinationPath, LEN(@DestinationPath)-1)  
  43.    
  44. -- Build the FTP script file.  
  45. select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile  
  46. EXEC master..xp_cmdshell @Command  
  47. select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile  
  48. EXEC master..xp_cmdshell @Command  
  49. select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile  
  50. EXEC master..xp_cmdshell @Command  
  51. select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile  
  52. EXEC master..xp_cmdshell @Command  
  53. IF LEN(@FTPMode) > 0  
  54. BEGIN  
  55.     select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile  
  56.     EXEC master..xp_cmdshell @Command  
  57. END  
  58. select @Command = 'echo ' + 'lcd ' + @DestinationPath + ' >> ' + @tempdir + @workfile  
  59. EXEC master..xp_cmdshell @Command  
  60. IF LEN(@SourcePath) > 0  
  61. BEGIN  
  62.     select @Command = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile  
  63.     EXEC master..xp_cmdshell @Command  
  64. END  
  65. select @Command = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile  
  66. EXEC master..xp_cmdshell @Command  
  67. select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile  
  68. EXEC master..xp_cmdshell @Command  
  69.    
  70. -- Execute the FTP command via above generated script file.  
  71. select @Command = 'ftp -s:' + @tempdir + @workfile  
  72. create table #a (id int identity(1,1), s varchar(1000))  
  73. print @Command  
  74. insert #a  
  75. EXEC master..xp_cmdshell @Command  
  76. select id, ouputtmp = s from #a  
  77.    
  78. -- drop table.  
  79. drop table #a  
  80. select @Command = 'del ' + @tempdir + @workfile  
  81. print @Command  
  82. EXEC master..xp_cmdshell @Command  

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

MPut Command
  1. -- FTP_MPUT.sql (Author Saddamhusen Uadanwala)  
  2. -- Transfer all files from an FTP server Direcoty using MPut command.  
  3.   
  4. DECLARE @FTPServer varchar(128)  
  5. DECLARE @FTPUserName varchar(128)  
  6. DECLARE @FTPPassword varchar(128)  
  7. DECLARE @SourcePath varchar(128)  
  8. DECLARE @SourceFiles varchar(128)  
  9. DECLARE @DestinationPath varchar(128)  
  10. DECLARE @FTPMode varchar(10)  
  11.    
  12. -- Attributes  
  13. SET @FTPServer = 'ftpserver'  
  14. SET @FTPUserName = 'username'  
  15. SET @FTPPassword = 'password'  
  16. SET @SourcePath = 'D:\Husen\Upload' -- Destination path.  
  17. SET @SourceFiles = '*.csv'  
  18. SET @DestinationPath = '' -- Folder path/Blank for root directory.  
  19. SET @FTPMode = 'binary' --  binary, ascii or blank for default mode.  
  20.    
  21. DECLARE @Command varchar(1000)  
  22. DECLARE @workfile varchar(128)  
  23. DECLARE @nowstr varchar(25)  
  24.    
  25. -- %TEMP% environment variable.  
  26. DECLARE @tempdir varchar(128)  
  27. CREATE TABLE #tempvartable(info VARCHAR(1000))  
  28. INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'  
  29. SET @tempdir = (SELECT top 1 info FROM #tempvartable)  
  30. IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'  
  31. DROP TABLE #tempvartable  
  32.    
  33. -- Generate @workfile  
  34. SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')  
  35. SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'  
  36.    
  37. -- Deal with special chars for echo commands.  
  38. select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')  
  39. select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')  
  40. select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')  
  41. select @DestinationPath = replace(replace(replace(@DestinationPath, '|', '^|'),'<','^<'),'>','^>')  
  42. IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'  
  43.    
  44. -- Build the FTP script file.  
  45. select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile  
  46. EXEC master..xp_cmdshell @Command  
  47. select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile  
  48. EXEC master..xp_cmdshell @Command  
  49. select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile  
  50. EXEC master..xp_cmdshell @Command  
  51. select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile  
  52. EXEC master..xp_cmdshell @Command  
  53. IF LEN(@FTPMode) > 0  
  54. BEGIN  
  55.     select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile  
  56.     EXEC master..xp_cmdshell @Command  
  57. END  
  58. IF LEN(@DestinationPath) > 0  
  59. BEGIN  
  60.     select @Command = 'echo ' + 'cd ' + @DestinationPath + ' >> ' + @tempdir + @workfile  
  61.     EXEC master..xp_cmdshell @Command  
  62. END  
  63. select @Command = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile  
  64. EXEC master..xp_cmdshell @Command  
  65. select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile  
  66. EXEC master..xp_cmdshell @Command  
  67.    
  68. -- Execute the FTP command via above generated script file.  
  69. select @Command = 'ftp -s:' + @tempdir + @workfile  
  70. create table #a (id int identity(1,1), s varchar(1000))  
  71. insert #a  
  72. EXEC master..xp_cmdshell @Command  
  73. select id, ouputtmp = s from #a  
  74.    
  75. -- drop table.  
  76. drop table #a  
  77. select @Command = 'del ' + @tempdir + @workfile  
  78. 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.

Now, refer this link and try other commands http://www.nsftools.com/tips/MSFTP.htm by yourself.
Next Recommended Reading Start SQL Server Management Studio using Run command