For the ones unware of SFTP (SSH File Transfer Protocol), it is a secure channel with SSH2 encryption established between two machines. You can think of it as FTP with encryption and a few more differences. For more information about this you can read this blog post.
There are some alternatives to get this done, ones paid and others free. I will focus on the use of the excellent SSH.NET because I couldn’t find a good guide to get it to work with SSIS.
It’s worth mencioning WinSCP as well, and the excellent guide to make it work with Integration Services.
The recommended way of installing SSH.NET is by using Nuget, but the only way to SSIS to be able to access the library is installing the library to the GAC.
You can see two things in my code that are worthy of note. The first one was the file I had to download and the second the problems with the way security was implemented in the server.
For the first one I created the selectedZip variable that contains the name of the correct file to download. The second forced me to create an event to simulate the keyboard typing the password on the server.
You can see that on the code below that you can paste to a Script Task. Don’t forget to add the following DLL references: