title: Transfer file to a ftp server using SQL Server script
body: <p>I want to generate a file, using SQL Server script, and then transfer that file to a ftp server.</p>
  
  <p>The following script works very well when the encryption configured is "use plain ftp". Unfortunately the same script don't work if the encryption is "require explicit ftp over TLS".</p>
  
  <p>Can anyone know how can I modified the script (what should I add) to let it work?</p>
  
  <p>Thanks in advance</p>
  
  <pre><code>declare @bcpcmd varchar (4000), @file varchar( 20)
  
  set @file = 'File_' +  cast (year( getdate()) as varchar ) + '_'+   right('0' + cast(month (getdate()) as varchar),2 )+  '_' +  right('0'+ cast( day(getdate ()) as varchar),2 ) + '_' + right('0' + cast(datepart (Hour, getdate()) as varchar ),2)+ '_' + right('0' + cast (datepart( minute,getdate ()) as varchar), 2)
  
  set @bcpcmd = 'bcp "select * from mydb.dbo.customer" queryout C:\Web\'+@file+ '.csv'+' -t^; -T -c'
  
  EXEC master ..xp_cmdshell @bcpcmd
  
  DECLARE @FTPServer varchar (128)
  DECLARE @FTPUser varchar (128)
  DECLARE @FTPPwd varchar (128)
  DECLARE @SourcePath varchar (128)
  DECLARE @SourceFiles varchar (128)
  DECLARE @DestPath varchar (128)
  DECLARE @FTPMode varchar (10)
  
  -- FTP attributes.
  SET @FTPServer = 'ftp.xxxx.com'
  SET @FTPUser = 'myLogin'
  SET @FTPPwd = 'myPwd'
  SET @SourcePath = 'C:\Web'
  SET @SourceFiles = @file+'.csv'
  SET @DestPath = 'Sources' -- Destination path. Blank for root directory.
  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 ) &lt;&gt; '\' 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, '|', '^|'),'&lt;' ,'^&lt;'), '&gt;','^&gt;' )
  select @FTPUser = replace( replace(replace (@FTPUser, '|', '^|'), '&lt;','^&lt;' ),'&gt;', '^&gt;')
  select @FTPPwd = replace( replace(replace (@FTPPwd, '|', '^|'), '&lt;','^&lt;' ),'&gt;', '^&gt;')
  select @DestPath = replace( replace(replace (@DestPath, '|', '^|'),'&lt;' ,'^&lt;'), '&gt;','^&gt;' )
  IF RIGHT( @SourcePath, 1 ) &lt;&gt; '\' SET @SourcePath = @SourcePath + '\'
  
  -- Build the FTP script file.
  select @cmd = 'echo ' + 'open ' + @FTPServer + ' &gt; ' + @tempdir + @workfile
  EXEC master ..xp_cmdshell @cmd
  select @cmd = 'echo ' + @FTPUser + '&gt;&gt; ' + @tempdir + @workfile
  EXEC master ..xp_cmdshell @cmd
  select @cmd = 'echo ' + @FTPPwd + '&gt;&gt; ' + @tempdir + @workfile
  EXEC master ..xp_cmdshell @cmd
  select @cmd = 'echo ' + 'prompt ' + ' &gt;&gt; ' + @tempdir + @workfile
  EXEC master ..xp_cmdshell @cmd
  IF LEN (@FTPMode) &gt; 0
  BEGIN
         select @cmd = 'echo ' + @FTPMode + ' &gt;&gt; ' + @tempdir + @workfile
         EXEC master ..xp_cmdshell @cmd
  END
  IF LEN (@DestPath) &gt; 0
  BEGIN
         select @cmd = 'echo ' + 'cd ' + @DestPath + ' &gt;&gt; ' + @tempdir + @workfile
         EXEC master ..xp_cmdshell @cmd
  END
  select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFiles + ' &gt;&gt; ' + @tempdir + @workfile
  EXEC master ..xp_cmdshell @cmd
  select @cmd = 'echo ' + 'quit' + ' &gt;&gt; ' + @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
  </code></pre>