FTP Scripts

There is always a need to FTP files to and from an SQL Server. The following scripts allow you to quite easily do this.
Both of the following scripts have an attributes section where you enter the various details about what FTP server you are using, the authentication, the file(s) being transferred, and their destination.
Even though these scripts can be used for transferring multiple files, you can just specify a single file without a wild card.

Caveats:

• These scripts will only transfer files between the SQL Server the script is running on and the FTP server. It will not transfer files to/from your PC.
• Unfortunately, the command-line FTP program that comes with Windows is very basic. As such, the return code is always zero which indicates "success" even if the FTP script encounters a problem like an incorrect FTP server name.
• Your FTP server may be UNIX based, so be aware that path and files names are case-sensitive. If the case is wrong, it won't find the file or path.

Getting Files from an FTP Server using FTP_MGET.sql

This script is used to transfer one or files from an FTP server to a specified path on the SQL Server.

FTP_MGET.zip

-- FTP_MGET.sql (Written by John Buoro)
-- Transfer multiple files from an FTP server using MGET.

DECLARE @FTPServer varchar(128)
DECLARE @FTPUser varchar(128)
DECLARE @FTPPwd varchar(128)
DECLARE @SourcePath varchar(128)
DECLARE @SourceFiles varchar(128)
DECLARE @DestPath varchar(128)
DECLARE @FTPMode varchar(10)

-- FTP attributes.
SET @FTPServer = 'ftpserver'
SET @FTPUser = 'username'
SET @FTPPwd = 'password'
SET @SourcePath = '' -- Source path. Blank for root directory.
SET @SourceFiles = '*.LOG'
SET @DestPath = 'c:\Temp' -- Destination path.
SET @FTPMode = 'binary' -- ascii, binary or blank for default.
DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)

-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable

-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'

-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@DestPath, 1) = '\' SET @DestPath = LEFT(@DestPath, LEN(@DestPath)-1)

-- Build the FTP script file.
select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@FTPMode) > 0
BEGIN
	select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
	EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@SourcePath) > 0
BEGIN
	select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
	EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a

-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

Putting Files on an FTP Server using FTP_MPUT.sql

This script is used to transfer one or files from the SQL Server to a specified path on an FTP server.

FTP_MPUT.zip

-- FTP_MPUT.sql (Written by John Buoro)
-- Transfer multiple files to an FTP server using MPUT.

DECLARE @FTPServer varchar(128)
DECLARE @FTPUser varchar(128)
DECLARE @FTPPwd varchar(128)
DECLARE @SourcePath varchar(128)
DECLARE @SourceFiles varchar(128)
DECLARE @DestPath varchar(128)
DECLARE @FTPMode varchar(10)

-- FTP attributes.
SET @FTPServer = 'ftpserver'
SET @FTPUser = 'username'
SET @FTPPwd = 'password'
SET @SourcePath = 'c:\Temp'
SET @SourceFiles = '*.zip'
SET @DestPath = '' -- Destination path. Blank for root directory.
SET @FTPMode = 'binary' -- ascii, binary or blank for default.
DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)

-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable

-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'

-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
select @DestPath = replace(replace(replace(@DestPath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'

-- Build the FTP script file.
select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@FTPMode) > 0
BEGIN
	select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
	EXEC master..xp_cmdshell @cmd
END
IF LEN(@DestPath) > 0
BEGIN
	select @cmd = 'echo ' + 'cd ' + @DestPath + ' >> ' + @tempdir + @workfile
	EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a

-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

Products | Services | Contact Us | Privacy Policy | Donate | © 2010 VirtualObjectives