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

Advertisement

joerobbi
Guest

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?

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,378
Location:
Prague, Czechia

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

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

To generate the session log file, use /log=path_to_log_file command-line argument. Submit the log with your post as an attachment. Note that passwords and passphrases not stored in the log. You may want to remove other data you consider sensitive though, such as host names, IP addresses, account names or file names (unless they are relevant to the problem). If you do not want to post the log publicly, you can mark the attachment as private.

Reply with quote

joerobbi
Guest

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

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.
Description: Log from successful execution

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
41,378
Location:
Prague, Czechia

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

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?

Reply with quote

joerobbi
Guest

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

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!!

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,378
Location:
Prague, Czechia

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

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

Reply with quote

Advertisement

You can post new topics in this forum