Connecting with SFTP using SSIS

As you might already know, you can connect with a FTP server using SSIS FTP component. Unfortunately, SSIS cannot connect with SFTP but with a litle bit of creativity and some googling you can beat that limitation.

First STEP, download the latest psftp.exe version here. Once you download it, create a new folder on your hard drive and copy it over there. Say something like "c:\tmpdestino".

Second STEP, add a EXECUTE PROCESS TASK on your transformation and into the EXECUTABLE field add the full path to the psftp utility: "c:\tmpdestino\psftp.exe".

On your arguments field, add a command line with all the arguments needed to your task:

[user]@[sftp_HOSTNAME] -pw [YOURPASSOWRD] -be -batch -b [YOURBATCHFILE]

Third STEP, create a batch file that executes all your tasks in the SFTP host. That will be the last reference in your arguments field. So if you need to browse to a FOLDER like "Myfolder" and copy all the files from it, your bat would be like this:

cd Myfolder

mget *

And that is it! Add the task to your package and do your thing.

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06