title: Detecting FTP timeout in Windows batch script
body: <p>I have a SQL Server Agent job scheduled to run daily.  The first of two steps calls an SSIS package, which writes the results of a query to a file.  The second step executes a batch file which renames the file to include the date, uploads the file to a partner's FTP site, moves the file to an archive directory and cleans up.  This normally works fine.</p>
  
  <p>Occasionally, however, no notification is sent that the job completed successfully.  When I log into the server, I see that ftp.exe is still running, but the file has not been uploaded nor moved to the archive.  When I terminate the FTP process, the job continues, moving the file and cleaning up.  A look at the job history reveals two different scenarios in these failed cases.  First, the connection is made, but the last FTP output seen is <code>150 Opening data channel for file transfer.</code>--there is no <code>226 Transfer OK</code> as there is for a successful job run.  The second scenario is that the connection is never established, so the script is then off and trying to log in when there is no connection open.</p>
  
  <p>Here is the script with host, user, and pass munged:</p>
  
  <pre><code>@echo off
  setlocal
  
  D:
  cd D:\Backups
  
  set file_name=ssis_output.txt
  set new_file_name=SDCL.%date:~-4,4%-%date:~-10,2%-%date:~-7,2%-00-00-00.txt
  
  set user=XXXXXXXXXXXXX
  set pass=XXXXXXXXXXXXX
  set host=XXXXXXXXXXXXX
  
  ren "%file_name%" "%new_file_name%"
  
  echo user %user%&gt; ftpcmd.dat
  echo %pass%&gt;&gt; ftpcmd.dat
  echo bin&gt;&gt; ftpcmd.dat
  echo put %new_file_name%&gt;&gt; ftpcmd.dat
  echo close&gt;&gt; ftpcmd.dat
  echo bye&gt;&gt; ftpcmd.dat
  ftp -n -s:ftpcmd.dat %host%
  del ftpcmd.dat
  move %new_file_name% data_files
  </code></pre>
  
  <p>What I would like to do is find a way to include some error detection in the batch file, so that if the FTP connection is either not established or times out before the transfer completes, the script recognizes that and fails, allowing the job to fail and send the appropriate notification.  Even better, a connection failure would lead to a few additional attempts after appropriate pauses before finally surrendering and reporting a failure.</p>
  
  <p><strong>EDIT</strong>:  I'm not opposed to installing a different freeware FTP client, such as WinSCP, if that would aid in arriving at a solution.</p>