We have seen in last tip to upload individual files via FTP using Binary transfer mode (http://beyondrelational.com/justlearned/posts/603/change-transfer-mode-from-ascii-to-binary-for-files-upload-via-ftp-sql-server.aspx). Here is the script to upload all files from source folder using FTP. In 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

Thank you.

Published under: SQL Server Tips ·  ·  ·  ·