1

I am unable to download any files using SSIS (Execute Process Task) and Winscp. I followed various forums and was able to create a SSIS package. I am not sure where I am going wrong.

Steps I followed:- 

 Execute Process Task :- 
i) In Executable I wrote "C:\Program Files (x86)\WinSCP\WinSCP.exe"
ii)In Arguments :- /script=C:\Users\abcd\Desktop\efgh\remotelocal.txt
iii) Working directory :- C:\Users\abcd\Desktop\efgh (I believe this is required for the location where file needs to be saved)

In Remotelocal.txt I have the following script:-

option batch abort
option confirm off
open ftpes://ghteyeriygvs:%69%315694598665@00.000.000.000/ (this is dummy value)
option transfer binary
cd ./wfgh/
get *.* C:\Users\abcd\Desktop\egfh
close
exit

On Executing this package I get following error

[Execute Process Task] Error: In Executing "C:\Program Files (x86)\WinSCP\WinSCP.exe" "/script=C:\Users\abcd\Desktop\efgh\remotelocal.txt" at "C:\Users\abcd\Desktop\efgh", The process exit code was "1" while the expected was "0".

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
mehtat_90
  • 586
  • 9
  • 29
  • 1
    See [Troubleshooting WinSCP scripting/automation issues](https://winscp.net/eng/docs/troubleshooting#scripting). – Martin Prikryl Feb 15 '18 at 17:08
  • You can get the rest of the error details by supplying a variable to `StandardErrorVariable` in the `Execute Process Task`. Then write this variable to the SSIS logs or just read it while debugging the package – Mark Wojciechowicz Feb 15 '18 at 20:54

1 Answers1

2
get *.* C:\Users\abcd\Desktop\egfh

Hm. I'm not sure if that's a valid command. It may need to be:

get *.* C:\Users\abcd\Desktop\egfh\

Or:

lcd C:\Users\abcd\Desktop\egfh\
get *.*

However, the problem with troubleshooting this is that it's WinSCP throwing the error. That error cannot be logged by SSIS, but you can tell WinSCP to create a log.

In SSIS, in the Execute Process Task, change the arguments for WinSCP from this:

/script=C:\Users\abcd\Desktop\efgh\remotelocal.txt

To something like this:

/script=C:\Users\abcd\Desktop\efgh\remotelocal.txt /xmllog="M:\SSIS\Alexandria Patron Export\WinSCPLog.xml"

I choose the XML log over the standard log because the standard log is much more verbose and harder to see what's going on. If you want that log instead, try:

/script=C:\Users\abcd\Desktop\efgh\remotelocal.txt /log="M:\SSIS\Alexandria Patron Export\WinSCPLog.log"

Now try running your package and see what errors are logged by WinSCP.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Though for debugging problems, the standard log is imo better. +1 anyway – Martin Prikryl Feb 15 '18 at 17:05
  • @MartinPrikryl Eh, the regular log is more of a debug log, which I never need, but obviously makes sense for the application's developer! When I get an error from WinSCP, the issues invariably fall under "temporary network issue" or "stupid vendor changed the name of the file and didn't tell me" or "stupid vendor invalidated their SHA fingerprint and didn't tell me". I don't have to worry about "stupid vendor has quirky SFTP implementation." I only ever care where the error if the connection and file transfers succeed or if it fail, and that's all the XML log captures. – Bacon Bits Feb 15 '18 at 17:18
  • >Too many parameters for command 'cd'. Any idea why am I getting this error? – mehtat_90 Feb 15 '18 at 17:56
  • 1
    @mehtat_90 If the path has spaces in it, be sure to enclose it in double quotes. – Bacon Bits Feb 15 '18 at 18:29