Error in execute process task while uploading a file to sftp server using PSFTP.exe

Question

I created a execute process task in SSIS to upload a file to a sftp server. I have full access to the server folder and was able to drag and drop files using filezilla. I am using putty sftp client psftp.exe to do the upload. Now when I use the psftp.exe with user and pass and batch arguments, it works perfectly fine in windows cmd. But when I execute the same in SSIS, I get the following error:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:\temp\psftp.exe" "user -pw pass -be -batch -b Upload.bat" at "C:\temp", The process exit code was "1" while the expected was "0".
Task failed: Execute Process Task

My windows cmd arguments are :

psftp.exe [email protected] -b UploadUsersToSftp.bat

and then it asks for password and after entering password, file upload is done successfully. But I am having trouble performing same through SSIS. Below is the SSIS process task argument screenshot:

I searched online and found some similar questions at here but nothing specific to psftp. Also found same question on stackoverflow here but the solution mentioned is permissions, which I already checked.

Any help would be appreciated.

Answer 1

The SSIS most probably runs under a different local account than the one you use in "Windows cmd".

An SFTP access requires confirmation of a host key. You have probably verified the host key in your local account in the past, so it is cached in Windows registry.

But the account that runs SSIS does not have the host key cached. So it must fail.

You should add -hostkey switch to psftp.exe command line with the fingerprint of the expected host key.

---

If that does not help, wrap the psftp.exe to a batch file and redirect its output to a file.

psftp.exe [email protected] -b UploadUsersToSftp.bat > c:\temp\psftp.out 2>&1

And run the batch file in SSIS, instead of psftp.exe directly.

Then check the file for errors.