You are here:Home » upload » Upload multiple files from source folder via FTP in SQL Server

Upload multiple files from source folder via FTP in SQL Server

Here is the script to upload all files from source folder using FTP. In the script I just added command to set source folder path and destination folder path, used mput command to put all files instead of individually.
DECLARE 
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@workfilename varchar(128),
@cmd varchar(1000)

SELECT @workfilename = 'ftpcmd.txt'

SET @workdir = 'C:\FTP\'
SET @FTPServer = 'Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'FTP User Password'
SET @SourcePath = 'Source File Path'
SET @FTPPath = 'File Path'
-- We don't need file name as moving all files
-- SET @SourceFile = 'Source File Name'
-- SET @FTPFileName = 'File Name'

SELECT @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd

-- Changing trnasfer mode from default to BINARY
SELECT @cmd = 'echo BIN' + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd

----------------- Start Change --------------------------------------------
-- Setting destinaton ftp path 
SELECT @cmd = 'echo cd '+ @FTPPath  + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd 
              
-- Setting source path               
SELECT @cmd = 'echo lcd '+ @SourcePath  + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd 

-- SELECT @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
-- Putting all files from source folder path to destination folder path
SELECT @cmd = 'echo ' + 'mput *.bak >> ' + @workdir + @workfilename                  
EXEC master..xp_cmdshell @cmd 
----------------- End Change ----------------------------------------------

SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'ftp -i -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd
GO
This reference is taken from here. Thank you for reading this post. Stay tuned for more.