You are here:Home » upload » Change transfer mode from ASCII to BINARY for files upload via FTP - SQL Server

Change transfer mode from ASCII to BINARY for files upload via FTP - SQL Server

Recently I have shared a script to upload files via FTP in SQL Server, And I also mentioned that it will use ASCII transfer mode. Here I want to revise that script which will use BINARY transfer mode instead of ASCII. Please check out below are updated script with the addition of some code.
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 @SourceFile = '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
----------------- Added new command -----------------------
-- Change trnasfer mode from default to BINARY
SELECT @cmd = 'echo BIN' + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd
----------------- End Added --------------------------------
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
This reference is taken from here. Hope you like this.