I want to share the script to upload files via FTP in SQL Server which need work directory to create a 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

Published under: SQL Server Tips ·  ·  ·  ·