FTP Files using SQL Server

In previous post we discussed BCP command and how it can be used to generate a csv file to a specified location. Today, we will see how you can FTP file created via BCP command to a given FTP Server. If you haven't done so, I suggest you review the previous post as we will build on that.

Note, this nice little stored procedure was originally developed by Nigel Rivett, although I have modified it a bit to better explain it here.

Previously, we created a file called "payroll.csv" in C:\Temp\Folder. Today, we will FTP this file to an FTP Server.

Lets create a stored procedure which will accept FTP parameters, file to FTP and then use xp_cmdshell to ftp the file.

CREATE PROCEDURE [dbo].[sp_FTP]
    @ServerName      varchar(50) ,
    @UserName        varchar(50) ,
    @Password        varchar(50) ,
    @FilePath        varchar(255) ,
    @FileName        varchar(255) ,
    @SourcePath      varchar(255) ,
    @SourceFile      varchar(255) ,
    @WorkingDir      varchar(255),
    @CommandFile     varchar(50)
AS

DECLARE @SQL varchar(1000)

--Since we want to echo the output, we need to replace some special characters
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 @SQL= 'echo ' + 'open ' + @ServerName + ' > ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @UserName + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @Password + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FilePath + @FileName + ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'quit' + ' >> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @cmd = 'ftp -s:' + @WorkingDir + @CommandFile

CREATE TABLE #tempTable (ID INT IDENTITY(1,1), [Command] varchar(1000))
insert #tempTable
exec master..xp_cmdshell @SQL

select ID, ouputtmp = [Command] from #tempTable

@CommandFile parameter accepts a text file that will have the FTP command to execute

For example

open myftp.ftp.com
testuser
testpass
put c:\temp\payroll.csv payroll.csv
quit

Remember, xp_cmdshell is disabled by default. You can enable it by running the following script.

RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

You can call this stored procedure in this way...

EXEC sp_FTP
    @ServerName = 'myftp.ftp.com' ,
    @UserName   = 'testuser' ,
    @Password   = 'testpass' ,
    @FilePath   = '' ,
    @FileName   = 'payroll.csv' ,
    @SourcePath = 'C:\temp\' ,
    @SourceFile = 'payroll.csv' ,
    @WorkingDir = 'C:\temp\',
    @CommandFile = 'FTPCommand.txt'

Although not ideal, this is a nice little feature that you can use via xp_cmdshell to FTP files.

Thank you.