You are here:Home » upload » Script to upload files via FTP in SQL Server

Script to upload files via FTP in SQL Server

I want to share the script to upload 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 upload 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),
@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 @SourceFile = 'Source File Path'
SET @SourceFile = 'Source File Name'
SET @FTPPath = 'File Path'
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
SELECT @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd
GO
This reference is taken from here.