Script to download file via FTP in SQL Server by serverku

I want to share the script to download files via FTP in SQL Server, which need work directory to create an FTP command file to execute. This script will use ASCII transfer mode as a default to download files.

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),
 @cmd varchar(1000),
 @workfilename varchar(128)

SET @FTPServer = 'FTP Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'Password'
SET @FTPPath = '/subfolder1/subfolder2/' -- or '' if not having subfolders
SET @FTPFileName = 'FTP File Name'
SET @SourcePath = 'Local Path for download'
SET @SourceFile = 'Local File Name to be saved as'
SET @workdir = 'C:\FTP\'
SET @workfilename = 'ftpcmd.txt'
 
-- Writing steps to working file
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
SELECT  @cmd = 'echo '+ 'get ' + @FTPPath + @FTPFileName + ' ' + @SourcePath + @SourceFile+ ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ 'quit'+ ' >> ' + @workdir + @workfilename
-- Executing steps from working file
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd

This reference is taken from here.