SFTP file transfer in SSIS

Question

I've read a whole bunch of different forums on this but cant seem to get mine to work. I need to transfer a file from my local machine to an SFTP server using SSIS. Here is what I've already done:

• I downloaded WinSCP
• Created a saved connection in WinSCP (TS_NEW)
• Open up that saved connection (to verify)
• Copied WinSCP.exe and WinSCP.com to the SSIS project folder
• Created Text file with the below script.

option batch on
option confirm off
open sftp://TS_NEW.com
cd C:\Users\zaccheut\Documents\Analytics Project 2018\ThoughtSpot\SSIS File
option transfer binary
put FCT_DC_MONTH.csv  /export/xvdb1/skynet/File_Test
exit

In SSIS my Execute Process Task editor has this:

• Executable: C:\Users\zaccheut\Documents\Visual Studio 2012\Projects\Skynet Refreshes\Skynet Refreshes\WinSCP.com

• Arguments: /script=C:\Users\zaccheut\Documents\Visual Studio 2012\Projects\Skynet Refreshes\Skynet Refreshes\local2remote.txt

How do I test this in Command Line, and what am I doing wrong?

Answer 1

You didn't really explain us what (if anything) goes wrong.

In general, problems like this are covered in WinSCP FAQ My script works fine when executed manually, but fails or hangs when run by Windows Scheduler, SSIS or other automation service. What am I doing wrong?

See also WinSCP article SFTP Task for SSIS/SSDT.

Though, to be more concrete, there are three clear issues, addressed below.

---

You have created a WinSCP stored site in an interactive Windows session. By default WinSCP stores site information to HKCU hive of Windows registry. Your SSIS session has no access to that hive.

Ideally, you should create a standalone script, that does not depend on a stored configuration. That particularly mean that your open command should contain credentials and a fingerprint of hostkey, like:

open sftp://username:password@TS_NEW.com -hostkey=...

WinSCP GUI can generate such script for you.

---

Another problem is that your cd command should really be lcd and the path must be enclosed to quotes as it contains spaces.

lcd "C:\Users\zaccheut\Documents\Analytics Project 2018\ThoughtSpot\SSIS File"

---

Similarly, the path to the script in WinSCP arguments must be enclosed to quotes as it contains spaces:

/script="C:\Users\zaccheut\Documents\Visual Studio 2012\Projects\Skynet Refreshes\Skynet Refreshes\local2remote.txt"

---

You should also add /log switch to WinSCP arguments to enable session logging. That will help you a lot with debugging. Generated script (above) shows that too.