FTP through an MSSQL stored procedure

This stored procedure takes four parameters:

• file to ftp
• server name or IP
• ftp login
• ftp password

SP will ensure that local file exists before continuing, it will create and execute a batch file in order to push the file to the remote FTP server:

--exec example

EXEC dbo.up_FTPPushFile 'c:\temp\test.txt', 'server','user', 'password'
create proc up_FTPPushFile
@file_to_push varchar(255)
@ftp_to_server varchar(255)
@ftp_login varchar(255)
@ftp_pwd varchar(255)
as
Set Nocount On

--STEP 0
--Ensure we can find the file we want to send.
Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
Drop table #FileExists
RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)
RETURN 1
END


--STEP 1
--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push
--batch file will hold 4 records:
--1) login
--2) password
--3) ftp command and file to push
--4) exit command

declare @sql varchar(255), @cmd varchar(255), @batch_ftp varchar(255), @ret int
set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'
set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /T /c'
Create table ##temp_ftp_bat(ftp_batch varchar(255), file_to_push varchar(255))
Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)
Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)
Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)
Insert into ##temp_ftp_bat values ('bye', @file_to_push)
EXEC master.dbo.xp_cmdshell @cmd
Drop table ##temp_ftp_bat


--STEP 2
--Ensure we can find the batch file we just created.

Delete #FileExists
Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
Drop table #FileExists
RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)
RETURN 1
END
Drop table #FileExists


--STEP 3
--Execute newly created .bat file, save results of execution

Create table #temp_ftp_results (ftp_output varchar(255))
set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server
Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))
BEGIN
Drop table #temp_ftp_results
RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)
RETURN 1
END
Drop table #temp_ftp_results


--STEP 4
--delete batch file

set @cmd = 'del '+@batch_ftp
EXEC master.dbo.xp_cmdshell @cmd
go