Using WinSCP script for SFTP access from SSIS

0
votes

I am new to WinSCP and am attempting to create a script file that will eventually be used with SSIS to download files from an SFTP site. A lot of the literature WinSCP includes explains the file downloading or uploading portions. For the time being, I just want to create a script to test the connection first and will build from there.

So far I saved the connection in WinSCP and have the following. The below code does not seem to function at all and I am not sure where else to go as I am still reading about the scripting for WinSCP. Is there a way or can someone point me in a direction to see if I am in fact connecting via through the script?

option batch on 
option confirm off 
open username@address 
exit
2
scriptingssiswinscp
What's the actual question/problem now? - ConcurrentHashMap

2 Answers

0
votes

Not sure what SSIS is (sorry) but I can tell you how I'd set it up from a windows batch file if that helps:

If you are open to using a different software, consider using cygwin. It mimics a linux shell so linux users on windows have a lot of linux utilities handy. That being said, there are some commands which can run on windows straight from command prompt (and thus batchable). What you'd need to do:

1) install cygwin 2) Create a "passwordless" login (using ssh-rsa authentication). To do this start your cygwin terminal and use the commands "ssh-keygen" and "ssh-copy-id" (more on that later) 3) Now you can run "sftp" from the DOS command prompt (does not require cygwin terminal) and sftp to your account. No password required because of step 2).

A few follow up info:

What can run from dos command prompt and what must be run from cygwin terminal?

If you go to the "bin" directory of cygwin (for me it's in c:\cygwin\bin) you can see all the cygwin utilities. Anything with "exe" extension can be run from dos command prompt. If no "exe" extension, must start cygwin terminal first

How to set up ssh-rsa authentication?

You can pretty much google "ssh login without password" and pull up a lot of results. This is common for setting up login from one linux system to another. You would be using the same steps using cygwin on windows. My instructions are here:

http://geekswing.com/geek/unix/how-to-ssh-login-without-a-password-using-ssh-keygen-quick-tutorial/

0
votes

Storing session settings in WinSCP GUI and trying to access them from WinSCP script running in SSIS is generally a bad idea. I believe there's no example or guide on WinSCP site that would suggest doing that.

WinSCP stores its configuration in registry in HKEY_CURRENT_USER hive. The SSIS typically runs under a dedicated system account, that have its own HKEY_CURRENT_USER hive, and won't see the GUI configuration.

For details see WinSCP FAQ about your problem:
https://winscp.net/eng/docs/faq_scheduler

The best you can do is isolate your your script from configuration by using the session URL with the open command, instead of the stored site name.
See also https://winscp.net/eng/docs/scripting#configuration

Your actual problem can be completely different though. But that's hard to guess as you have not shared any details, such as error message, log file, etc.