Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2012-05-21 : 06:52:23
|
I am using the following script below to download a file from an FTP site. I am trying to change it to work in pasive mode. I have commented out what I was trying.I would be very glad it someone to help me adjust the scriptALTER procedure [dbo].[UDEF_KD_s_ftp_GetFile]-- FTP_MGET.sql (Written by John Buoro)-- Transfer multiple files from an FTP server using MGET.@FTPServer varchar(128),@FTPUser varchar(128),@FTPPwd varchar(128),@SourcePath varchar(128),@SourceFiles varchar(128),@DestPath varchar(128),@FTPMode varchar(10)as-- FTP attributes./*SET @FTPServer = 'ftp.easyorder.eu'SET @FTPUser = 'hbsgroup'SET @FTPPwd = 'H85gtop'SET @SourcePath = '/in/' -- Source path. Blank for root directory.SET @SourceFiles = '44272_catalogue.csv'SET @DestPath = 'D:\WebFiles\' -- 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 @workfileSET @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 + @workfileEXEC master..xp_cmdshell @cmdselect @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdselect @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdselect @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdIF LEN(@FTPMode) > 0BEGIN select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile EXEC master..xp_cmdshell @cmdEND--select @cmd = 'echo ' + 'quote pasv' -- literal--+ ' >> ' + @tempdir + @workfile--exec master..xp_cmdshell @cmdselect @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdIF LEN(@SourcePath) > 0BEGIN select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile EXEC master..xp_cmdshell @cmdENDselect @cmd = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdselect @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmd-- Execute the FTP command via script file.select @cmd = 'ftp -s:' + @tempdir + @workfilecreate table #a (id int identity(1,1), s varchar(1000))insert #aEXEC master..xp_cmdshell @cmdselect id, ouputtmp = s from #a-- Clean up.drop table #aselect @cmd = 'del ' + @tempdir + @workfileEXEC master..xp_cmdshell @cmdGO |
|