FTP files using SQL stored procedure
This stored procedure takes four parameters
1)file to ftp
2)server name or IP
3)ftp login
4)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 3
--delete batch file
set @cmd = 'del '+@batch_ftp
EXEC master.dbo.xp_cmdshell @cmd
go 
