How We can Download Data From Remote Through SSIS Using SFTP

For Downloading data using SFTP we need to follow below steps.

1. We need to use a free tool called WinSCP which provides a command line utility to communicate with SFTP sites. Install WinSCP.

2. The Command line utility is located at C:\Program Files\WinSCP\WinSCP.Com.

3. Create the script (C:\Download.txt)

  Download.txt
  ---------------
option batch on
option confirm off
open sftp://user:password@ipaddress:port  --- username,password( add : in fornt of password),ipaddress( add @ in fornt of ipaddress) and port number(add : in fornt of portnumber)
cd /mydir
option transfer binary
get *.dat c:\mylocaldir                   --- Download the files into c:\mylocaldir folder
mv *.dat ./backup/*                      --it is optional   --- Move the contents on remove folder
close
exit

4. Invoke the WinSCP.com  executable using Execute Process Task in SSIS

5. In Execute process task  in general
    Arguments: -script=C:\Download.txt
    Executable:C:\Program Files\WinSCP\WinSCP.com.