2

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.

Martin Prikryl
  • 167,268
  • 50
  • 405
  • 846
xChaax
  • 183
  • 5
  • 23
  • Can you use something like `rsync` to achieve this? – Ankirama May 05 '17 at 01:58
  • Or just a terminal and do something like `cd E:\localpath\; res=$(ls -r1 | head -n1); scp $res user@yourserver.tld` or `cd E:\localpath\; res=$(ls -r1 | head -n1); sftp user@yourserver.tld -b << EOF put $res EOF` – Ankirama May 05 '17 at 02:06
  • @Ankirama, need to use sftp instead of rsync, or is it possible to use rsync with sftp ? – xChaax May 05 '17 at 02:10
  • 1
    Thanks for reply, I'll try to both methods. – xChaax May 05 '17 at 02:12
  • Hi @Ankirama, did try but I dont think these are correct syntax. – xChaax May 05 '17 at 03:03
  • What message did you get ? For "EOF put $res EOF" you must put in in 3 lines: first: EOF, second: put $res and third: EOF. – Ankirama May 05 '17 at 03:08
  • 1
    Oh for crying out loud, how does **NOBODY** read the tag descriptions. @Ankirama, [batch-file] is **Windows-exclusive**; bash code isn't going to do a damn thing here except throw errors. – SomethingDark May 05 '17 at 03:18
  • To make it clear, I'm done editing my question. – xChaax May 05 '17 at 03:33
  • What SFTP client are you using? You have tagged PuTTY, what is SSH terminal. Mayb you are using PSFTP from PuTTY package. But PSFTP on the contrary has no `ascii` command. – Martin Prikryl May 05 '17 at 06:07
  • Hi @MartinPrikryl I just know that I'm using reflection for secure IT. – xChaax May 05 '17 at 06:55
  • Come on, you have to know how you *"call txt script in batch file"* - Show us the batch file! – Martin Prikryl May 05 '17 at 07:12
  • In .Bat file I got the ssh.ppk then -B(for batch mode-File from which to read commands) /path of txt script above/. – xChaax May 05 '17 at 07:13
  • Could you just show us the batch file? We need to know what application do you execute and pass those switches to! - Though as you are using .ppk file, it's probably the `psftp.exe` - on the other hand psftp has `-b`, not `-B` - So show us! Do not hide information from us, if you want us to help you! – Martin Prikryl May 05 '17 at 07:23
  • Hi @MartinPrikryl the question was amended to show the batch file. – xChaax May 05 '17 at 07:34
  • OK, so you are using some Windows build of OpenSSH `sftp`. So why did you tag PuTTY? - Anyway, as PuTTY psftp is modeled after OpenSSH `sftp`, my answer still stands. - Also note that OpenSSH `sftp` does not support .ppk files. So your key file is not .ppk - It must be .pem or other. So it's quite confusing to name it .ppk (even though you it might have been .ppk originally, you must have converted it). – Martin Prikryl May 05 '17 at 09:38
  • And note that neither OpenSSH `sftp` supports the `ascii` command. You must be getting an error for that. WinSCP does support ascii/text mode though. See my answer. – Martin Prikryl May 05 '17 at 09:46
  • And even OpenSSH `sftp` has `-b`, not `-B`. – Martin Prikryl May 05 '17 at 09:47
  • Thanks for the great explanation, I tagged PuTTY cause I m using the ppk file in batch file. And as per `ascii` command it doesn't giving an error when executing this Batch file and succesfully put all the file into the sftp server using `*.csv` command in my `File.txt` above. – xChaax May 05 '17 at 09:52
  • You maybe using some custom build of `sftp`. But how can we know, if you do not know yourself! - Anyway, does my answer help? – Martin Prikryl May 05 '17 at 10:00

2 Answers2

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:

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://username@example.com/ -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:

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)

Community
  • 1
  • 1
Martin Prikryl
  • 167,268
  • 50
  • 405
  • 846
0

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

Geoff Griswald
  • 730
  • 8
  • 18