March 6, 2012 at 9:26 am
I have been using the following script to download from a ftp site. It has been working great until we have changes to joining are 2 broadbands together (sharedband)
Since then I have got an error "ORT Not Understood"
From looking on the web it appears we might need to put it as pasive mode. How would I do this?
Glad of any help ....:w00t:
ALTER procedure [dbo].[UDEF_KD_s_ftp_GetFile2]
-- 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 = '*****'
SET @FTPPwd = *****
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 @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 ' + 'get ' + @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
GO
March 7, 2012 at 3:39 am
Can anyone help?
January 15, 2018 at 6:33 am
KWD
January 15, 2018 at 1:47 pm
The documentation for the command-line FTP in Windows does not show a parameter to specify passive mode.
You can set passive mode operation on an FTP task in an SSIS package,
You could use an FTP_Client object in .Net code in a CLR procedure.
cURL can be used to add more bells and whistles to an FTP script, and supports passive-mode http://FTP. This would still be called via xp_cmdshell.
January 16, 2018 at 4:49 pm
I would highly recommend using the command line version of WinSCP, called WinSCP.com. It works great, can handle "passive mode", and SFTP and is also freely available.
January 16, 2018 at 5:39 pm
Paul Bukowski - Tuesday, January 16, 2018 4:49 PMI would highly recommend using the command line version of WinSCP, called WinSCP.com. It works great, can handle "passive mode", and SFTP and is also freely available.
I agree. Using SQL script to do FTP job is not the right way to go. It seems that you are trying to delete a file once downloaded. Powershell can do a much better job. In fact, you didn't need to delete the source file. You should move it to a 'downloaded' or'archive' folder on the FTP server just in case you need to download it again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply