Executing FTP Commands from SQL Server

Below Code will execute FTP Commands from SQL Server. First thing you need to take care is create a text file with the name “ftp” and type all your ftp commands and then create the below procedure in SQL Server

-- =============================================
-- Author:  V.U.M.Sastry Sagi
-- Create date:12/28/2010
-- Description: Executes a FTP Command file from SQL Server
-- =============================================

Create procedure [dbo].[GetFTPData]
@FTPServer	varchar(128) ,
@FTPUser	varchar(128) ,
@FTPPWD	varchar(128) ,
@FTPPath	varchar(128) ,
@workdir	varchar(128)
as

declare	@cmd varchar(1000)
declare @workfilename varchar(128)
	
	select @workfilename = 'ftp.txt'
	
	-- deal with special characters for echo commands
	select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
	select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
	select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
	select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')
	
	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 '					+ 'dir ' + @FTPPath
			+ ' >> ' + @workdir + @workfilename
	exec master..xp_cmdshell @cmd
	select	@cmd = 'echo '					+ 'quit'
			+ ' >> ' + @workdir + @workfilename
	exec master..xp_cmdshell @cmd
	
	select @cmd = 'ftp -s:' + @workdir + @workfilename
	
	create table #tempTable (id int identity(1,1), source varchar(1000))
	insert #tempTable
	exec master..xp_cmdshell @cmd
	
	select id, source from #tempTable
	drop table #tempTable