SSIS Execute Process hangs in jobstep unless proxy user account is logged into the server

I have an SSIS package with an Execute Process step that runs winscp to pull files from a remote server. The step works fine, but only if the User ID for the Proxy being used to run the jobstep is physically logged into the server. To explain that further, assume we have two user IDs, MechID and UserID.

The step runs without issue:

- From BIDS, whether logged into the server as MechID or UserID.

- As a jobstep, if the jobstep proxy is MechID and we're logged into the server as MechID.

- As a jobstep, if the jobstep proxy is UserID and we're logged into the server as UserID.

- As a jobstep, if the jobstep proxy is MechID and we're logged into the server as UserID, and we have a second remote connection open that is logged into the server as MechID (doing nothing else in that connection).

- As a jobstep, if the jobstep proxy is UserID and we're logged into the server as MechID, and we have a second remote connection open that is logged into the server as UserID (doing nothing else in that connection).

The server has been up and running with SSIS and these user accounts for years without issue, so WinSCP is really the only new variable in the mix. Both user accounts have full permissions to the Putty directory, the directory where the private key is stored, and the target directory for the files being pulled. Is WinSCP maybe trying to access a directory or variable that's only created when the user logs into Windows?

Please attach a full session log file showing the problem (using the latest version of WinSCP).

My apologies, I forgot to note: when the failure occurs while attempting to run as a jobstep, no session log is generated. I've attached a session log from a successful run (when the proxy user is logged into the server), but not sure how much it will help.

OK, if you execute some plain batch file instead of WinSCP from your "jobstep", does it run? Would it create file, if you do echo something > c:\some\path\file?
If it does, what if you add this to the batch file
c:\path\to\winscp.com arguments > c:\some\path\winscp.out
What do you get in the winscp.out?

You are a genius! The batch output helped me find the issue. Turns out that, for WinSCP, you must specify the host key fingerprint using the -hostkey parameter unless you're logged into the server. Didn't occur to me until I saw the .out file contents. Thank you SO much!!

joerobbi wrote:

You are a genius! The batch output helped me find the issue. Turns out that, for WinSCP, you must specify the host key fingerprint using the -hostkey parameter unless you're logged into the server. Didn't occur to me until I saw the .out file contents. Thank you SO much!!

Covered here:
https://winscp.net/eng/docs/scripting#hostkey