maj 8 blog

Upload to FTP with T-SQL

Just a reminder script on how to upload files with T-SQL, and maybe someone else could use it.

 

DECLARE
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@workfilename varchar(128),
@cmd varchar(1000)

SELECT @workfilename = 'ftpcmd.txt'

SET @workdir = 'C:FTP'
SET @FTPServer = 'Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'FTP User Password'
SET @SourcePath = 'Source File Path'
SET @SourceFile = 'Source File Name'
SET @FTPPath = 'File Path'
SET @FTPFileName = 'File Name'

SELECT @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd
GO
Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+