In regular life of coding we are downloading data/files from server using c# code or any other code it may take much time to code as well as more time to download. This same thing you can be done using SQL server with 'Mput' command of FTP which is much lesser to code and will take a bit of time to download. Inverse process is also possible as you can use 'MPut' method.
You can use following script to download file(s) from FTP Server. Just pass your actual attributes and you have done, your files are downloaded.
MGet Command
-
-
-
- DECLARE @FTPServer varchar(128)
- DECLARE @FTPUserName varchar(128)
- DECLARE @FTPPassword varchar(128)
- DECLARE @SourcePath varchar(128)
- DECLARE @SourceFiles varchar(128)
- DECLARE @DestinationPath varchar(128)
- DECLARE @FTPMode varchar(10)
-
-
- SET @FTPServer = 'ftpserver'
- SET @FTPUserName = 'username'
- SET @FTPPassword = 'password'
- SET @SourcePath = ''
- SET @SourceFiles = '*.csv'
- SET @DestinationPath = 'D:\Husen\Download'
- SET @FTPMode = 'binary'
-
- DECLARE @Command varchar(1000)
- DECLARE @workfile varchar(128)
- DECLARE @nowstr varchar(25)
-
-
- 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
-
-
- SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
- SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'
-
-
- select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
- select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')
- select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')
- select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')
- IF RIGHT(@DestinationPath, 1) = '\' SET @DestinationPath = LEFT(@DestinationPath, LEN(@DestinationPath)-1)
-
-
- select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- IF LEN(@FTPMode) > 0
- BEGIN
- select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- END
- select @Command = 'echo ' + 'lcd ' + @DestinationPath + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- IF LEN(@SourcePath) > 0
- BEGIN
- select @Command = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- END
- select @Command = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
-
-
- select @Command = 'ftp -s:' + @tempdir + @workfile
- create table #a (id int identity(1,1), s varchar(1000))
- print @Command
- insert #a
- EXEC master..xp_cmdshell @Command
- select id, ouputtmp = s from #a
-
-
- drop table #a
- select @Command = 'del ' + @tempdir + @workfile
- print @Command
- EXEC master..xp_cmdshell @Command
You can use following script to Upload file(s) from local directory to FTP Server. Replace default attributes to actual attributes.
MPut Command
-
-
-
- DECLARE @FTPServer varchar(128)
- DECLARE @FTPUserName varchar(128)
- DECLARE @FTPPassword varchar(128)
- DECLARE @SourcePath varchar(128)
- DECLARE @SourceFiles varchar(128)
- DECLARE @DestinationPath varchar(128)
- DECLARE @FTPMode varchar(10)
-
-
- SET @FTPServer = 'ftpserver'
- SET @FTPUserName = 'username'
- SET @FTPPassword = 'password'
- SET @SourcePath = 'D:\Husen\Upload'
- SET @SourceFiles = '*.csv'
- SET @DestinationPath = ''
- SET @FTPMode = 'binary'
-
- DECLARE @Command varchar(1000)
- DECLARE @workfile varchar(128)
- DECLARE @nowstr varchar(25)
-
-
- 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
-
-
- SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
- SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'
-
-
- select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
- select @FTPUserName = replace(replace(replace(@FTPUserName, '|', '^|'),'<','^<'),'>','^>')
- select @FTPPassword = replace(replace(replace(@FTPPassword, '|', '^|'),'<','^<'),'>','^>')
- select @DestinationPath = replace(replace(replace(@DestinationPath, '|', '^|'),'<','^<'),'>','^>')
- IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'
-
-
- select @Command = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + @FTPUserName + '>> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + @FTPPassword + '>> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- IF LEN(@FTPMode) > 0
- BEGIN
- select @Command = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- END
- IF LEN(@DestinationPath) > 0
- BEGIN
- select @Command = 'echo ' + 'cd ' + @DestinationPath + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- END
- select @Command = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
- select @Command = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
-
-
- select @Command = 'ftp -s:' + @tempdir + @workfile
- create table #a (id int identity(1,1), s varchar(1000))
- insert #a
- EXEC master..xp_cmdshell @Command
- select id, ouputtmp = s from #a
-
-
- drop table #a
- select @Command = 'del ' + @tempdir + @workfile
- EXEC master..xp_cmdshell @Command
Here, we have made a simple text file with a list of commands using your parameters and executed it. Gentle reminder, commectivity and destination path should be there.
Now, refer this link and try other commands http://www.nsftools.com/tips/MSFTP.htm by yourself.