"Oh Dr Rivett! The poor wee man is quite exhausted I fear. I'm so glad you can come to help. My husband has been up at the PC for the past forty-eight hours trying to work out how to import data files from a remote FTP site. I'm beginning to fear that he has done himself an injury."
"Ahem, Mrs Muggins, we in the medical profession recognise this condition. URL-Aphasia it is called. An inability to remember good websites. Mr Muggins must know when to turn for help. There has bound to be someone on SQL Server Central or Simple Talk who has done it before and knows how to help"
"Oh Doctor, can you perhaps write us a sick-note, for poor Mr Muggins is almost a broken man"
"Not a bit of it. I can give you the solution myself" (rustling around in his doctor's bag) "Here is Dr 'Pop' Rivett's patent medicine to ensure that local directories are synchronised with remote FTP sites. You can adapt it to all sorts of problems. Mr Muggins will be right as rain by the morning, and rearing to get stuck into a day's work."
/* So often, one needs to get a datafeed into a SQL Server from an FTP site. It is common, for example, to have to get log files from a website via FTP and add these in to a SQL Server table for anaysis and reporting. The actual BCP process to get the file is pretty well understood but the process of synchronising a local directory with a remote FTP directory is less well understood. Something similar would be required if you were implementing a log shipping process across the internet. It is also very handy for archiving the contents of websites in cases where files are added but not changed.
In this example, we'll just use the ordinary FTP utility that is part of Windows. It is a command-line utility which hasn't changed much for years. The advantage is that it exists on every server and one does not need to do any installation. it is crude but sufficient
We'll
1/ find out if any new files exist on the FTP site that are not in the local directory
2/ get the file(s)
Firstly, we need a routine that talls us whay files are on the remote FTP site. This is done using FTP, putting the resulting list into a file, that we can subsequently read into a SQL Server table. This is done in a stpored procedure as follows */
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_ftp_GetDir]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_ftp_GetDir]
GO
CREATE PROCEDURE s_ftp_GetDir
@FTPServer VARCHAR(128),
@FTPUser VARCHAR(128),
@FTPPWD VARCHAR(128),
@FTPPath VARCHAR(128),
@workdir VARCHAR(128),
@LocalFile VARCHAR(128)
AS /*
exec s_ftp_GetDir
@FTPServer = 'www.myftpsite.com' ,
@FTPUser = 'myuser' ,
@FTPPWD = 'mypwd' ,
@FTPPath = '\dir\' ,
@workdir = 'c:\temp\'
@localfile = 'directoryList'
*/
SET nocount ON
DECLARE @cmd VARCHAR(1000)
DECLARE @workfilename VARCHAR(128)
SELECT @workfilename = 'ftpcmd.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, NO_OUTPUT
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + 'ls ' + @FTPPath + ' '
+ @Workdir + @localfile + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'ftp -s:' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
GO
/*
then we'll need a very similar routine in order to get a file from a remote FTP{ site
*/
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_ftp_GetFile]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_ftp_GetFile]
GO
CREATE PROCEDURE s_ftp_GetFile
@FTPServer VARCHAR(128),
@FTPUser VARCHAR(128),
@FTPPWD VARCHAR(128),
@FTPPath VARCHAR(128),
@FTPFileName VARCHAR(128),
@SourcePath VARCHAR(128),
@SourceFile VARCHAR(128),
@workdir VARCHAR(128)
AS /*
exec s_ftp_GetFile
@FTPServer = 'www.myftpsite.com' ,
@FTPUser = 'myuser' ,
@FTPPWD = 'mypwd' ,
@FTPPath = '' ,
@FTPFileName = 'myfile.html' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@SourceFile = 'myfile.html' ,
@workdir = 'c:\temp\'
*/
DECLARE @cmd VARCHAR(1000)
DECLARE @workfilename VARCHAR(128)
SELECT @workfilename = 'ftpcmd.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, NO_OUTPUT
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + 'gett "' + @FTPPath
+ @FTPFileName + '" "' + @SourcePath
+ @SourceFile + '" >> '
+ @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
SELECT @cmd = 'ftp -s:' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
GO
/* Now we can create a stored procedure that downloads any new file from the FTP site that we haven't got locally. Normally, you will want to put in all sorts of logging into the system to check on progress but here is the routine in sufficient detail to get you started.
If you are using this process for importing data into tables you may want to get files into the download directory if they appear in neither the download directory or an archive directory. This would be a simple addition. */
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_ftp_SyncDirectory]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_ftp_SyncDirectory]
GO
CREATE PROCEDURE s_ftp_SyncDirectory
@FTPServer VARCHAR(128),--the name of the FTP Server
@FTPUser VARCHAR(128),--the FTP user name
@FTPPWD VARCHAR(128),--the password
@FTPPath VARCHAR(128),--the path to the FTP directory ('' if none)
@SourcePath VARCHAR(128),--the local directory to synch to
@workdir VARCHAR(128) --the work directory
AS /*
exec s_ftp_SyncDirectory
@FTPServer = 'www.myftpsite.com' ,
@FTPUser = 'myuser' ,
@FTPPWD = 'mypwd' ,
@FTPPath = '' ,
@SourcePath = 'c:\vss\mywebsite\' ,
@workdir = 'c:\temp\'
*/
SET nocount ON
DECLARE @ii INT --iteration counter
DECLARE @iiMax INT
DECLARE @NameOfFile VARCHAR(2000)
DECLARE @command VARCHAR(8000)--used to assemble xp_CMDShell commands
DECLARE @FtpDIR VARCHAR(255) --the FTP directory
--temporary work tables
CREATE TABLE #filesOnServer ( filename VARCHAR(2000) )
CREATE TABLE #file ( line VARCHAR(2000) )
CREATE TABLE #filesLocal ( filename VARCHAR(2000) )
CREATE TABLE #filesToDo
(
FILE_ID INT IDENTITY(1, 1),
filename VARCHAR(2000)
)
SELECT @FTPDIR = CASE WHEN @FtpPath = '' THEN '.'
ELSE @FTPPath
END
--firstly we see what files there are on the FTP site
EXEC s_ftp_GetDir @FTPServer, @FTPUser, @FTPPWD, @ftpdir, @workdir, 'MyDir'
SELECT @command = 'type ' + @workdir + 'MyDir'
--and we read the list of files in the directory from the file
INSERT INTO #filesOnServer
( filename )
EXECUTE master..xp_cmdshell @Command
--check for errors
IF EXISTS ( SELECT *
FROM #filesonserver
WHERE filename LIKE '%Error occurred%' )
BEGIN
RAISERROR ( 'The work directory %s was not correct', 16, 1, @WorkDir )
RETURN 1
END
IF ( SELECT COUNT(*)
FROM #filesonserver
) < 3
BEGIN
RAISERROR ( 'That ftp site %s was empty or there was an error', 16, 1,
@FTPServer )
RETURN 1
END
--get the list of files in the local directory
SELECT @command = 'dir ' + @sourcePath
INSERT INTO #file
( line )
EXECUTE master..xp_cmdshell @Command
--and tease out all the files from what comes back
INSERT INTO #filesLocal
( filename )
SELECT SUBSTRING(line, 40, 255)
FROM #file
WHERE SUBSTRING(line, 39, 1) = ''
AND SUBSTRING(line, 40, 255) NOT IN (
'', '.', '..' )
--now we work out what filenames were on the FTP site but were NOT in the
--local directory
INSERT INTO #filesToDo
( filename )
SELECT #filesonserver.filename
FROM #filesonserver LEFT OUTER JOIN #filesLocal
ON #filesonserver.filename = #filesLocal.filename
WHERE #filesLocal.filename IS NULL
AND #filesonserver.filename IS NOT NULL
--get ready to iterate through the list
SELECT @ii = MIN(FILE_ID), @iiMax = MAX(FILE_ID)
FROM #filesToDo
--and fetch each one that is missing from the l;ocal directory.
WHILE @ii <= @iiMax
BEGIN
SELECT @NameOfFile = filename
FROM #filesToDo
WHERE FILE_ID = @ii
EXECUTE s_ftp_GetFile @FTPServer = @FTPServer, @FTPUser = @FTPUser,
@FTPPWD = @FTPPWD, @FTPPath = @FTPPath, @FTPFileName = @NameOfFile,
@SourcePath = @sourcePath, @SourceFile = @NameOfFile,
@workdir = @workdir
SELECT @ii = @ii + 1
END