Put only latest file into SFTP server in batch file

Question

I have an ETL (SSIS) job which creates a file in a folder named as TEST_20170505.csv before it sends the file to an SFTP server.

There would be multiple file in the folder such as, TEST_20170504.csv, TEST_20170503.csv. Currently I am using following sftp script file (File.txt) in a batch file.

lcd E:\localpath\
cd \sftpserverpath\
ascii
put *.csv
bye

This is my .bat file.

sftp -oIdentityFile=E:\sftp\filepath\ssh.ppk -B E:\sftp\filepath\File.txt username@ipaddress

But this will upload all the files in the local path to SFTP server instead of taking the latest/last modified/current date file.

Answer 1

OpenSSH sftp cannot do this on its own. But you can use some fancy batch file construct to select the latest file and then generate an ad-hoc sftp upload script.

Some references:

• How do I write a Windows batch script to copy the newest file from a directory?
• Windows batch file - Upload only latest file to FTP

---

Or use some more advanced Windows command-line SFTP client.

For example with WinSCP scripting, it's as easy as using -latest switch in the put command:

open sftp://[email protected]/ -privatekey=ssh.ppk
lcd E:\localpath\
cd \sftpserverpath\
ascii
put -transfer=ascii -latest *.csv
exit

Run the script (upload.txt) like:

winscp.com /script=upload.txt /ini=nul /log=upload.log

You can even have WinSCP generate the script/batch file for you (you just need to add the -latest switch manually).

References:

• Uploading the most recent file with WinSCP
• Converting OpenSSH SFTP script to WinSCP script

---

Note that WinSCP uses .ppk format of private key files. While OpenSSH sftp uses PEM format. Even though your key file is named .ppk, it cannot be real .ppk file, otherwise the sftp would reject it. You have probably converted original .ppk file to PEM, but incorrectly kept an original extension. You have to use the original .ppk file with WinSCP (if you do not have it, you can convert the PEM back to .ppk using PuTTYgen).

---

Also note that WinSCP actually supports the text/ascii mode (-transfer=ascii), while sftp does not (there's no ascii command).

---

(I'm the author of WinSCP)

Answer 2

The solution is instead of "put *.csv" in your script file, you specify the actual filename you want to upload