3

I have gone through the following guide to set up an SSIS package to retrieve a text file located on an SFTP server:

https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

To summarize, the SSIS package executes PSFTP.exe (A PuTTY tool) which takes the necessary credentials to connect to the server. It also takes a batch file that it executes after connecting. This batch file contains the commands to retrieve the desired text file. To start from the guide, it simply contains a cmd command to change directory, and a get command to retrieve the file:

cmd DataDump
get TeleMarketingResults.txt

All of this works fine.

The issue arises when I try to make this batch file logic more complex as it does not seem to recognize basic keywords. For instance, I would like to modify it to retrieve the most recent file, so I tried adding this:

for /f %%i in ('dir /b/a-d/od/t:c') do set LAST=%%i
echo The most recently created file is %LAST%

but then I get these errors:

psftp: unknown command "for"
psftp: unknown command "echo"

If I execute the batch file manually in a local directory, it works. The issue only occurs when passing it as a parameter to PSFTP.exe. Why is this?

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
Reddy
  • 481
  • 1
  • 7
  • 20
  • `PSFTP` is obviously reading the batch commands following the commands that it should act upon. Put the `FTP` commands into a file and use `PSFTP – Magoo Jun 18 '19 at 17:29
  • I cannot get passing the arguments from a file like that to work, is it supposed to be a .bat or .txt file? Do I include the '<'? – Reddy Jun 18 '19 at 19:25
  • 1
    While the suggestion by @Magoo can help in certain scenarios (like if you wanted to **upload** the latest **local** file), it won't help you in this case (**downloading** the latest **remote** file). You obviously wanted to run `dir` on the SFTP server. But it's not possible. I'm afraid you are wasting your time here. – Martin Prikryl Jun 18 '19 at 20:00

1 Answers1

2

psftp script file can contain psftp commands only. for, set or dir are not psftp commands.

There's hardly any reasonable way to retrieve latest file using psftp. You would have to do it in two steps. First to retrieve listing and store it to a file. Then parse that file using some smart batch file commands to find the latest files. And then run psftp again to download that file. It is cumbersome and ineffective as it requires two connections.

You better use a more powerful SFTP client. For example it's trivial with my WinSCP SFTP client. See

Martin Prikryl
  • 188,800
  • 56
  • 490
  • 992
  • 2
    I've actually been using the WinSCP GUI for basic FTP needs but didn't even realize the scripting capabilities it has, the "get -latest" command is certainly much more clean and user friendly, thanks so much for putting me on a much better path with this! – Reddy Jun 19 '19 at 14:54