Earlier i submitted script to upload file(s) via FTP in SQL Server here http://beyondrelational.com/justlearned/posts/600/script-to-upload-files-via-ftp-in-sql-server.aspx. You can find here the same but it's for download the file from FTP.

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

You can read earlier for the FTP here, http://beyondrelational.com/justlearned/posts/608/upload-all-files-from-source-folder-via-ftp-in-sql-server.aspx (to upload multiple files) and http://beyondrelational.com/justlearned/posts/603/change-transfer-mode-from-ascii-to-binary-for-files-upload-via-ftp-sql-server.aspx (to change file transfer mode).

Published under: SQL Server Tips ·  ·  ·  ·