How to Use SSIS with WinSCP to Download Files via SFTP

In today’s adventure, I will guide you on how to create a package that you can use to automate file transfers using SFTP in SSIS. Yes, I know API…API…. API. Not everyone has the infrastructure or skill set to handle APIs. Similarly there are still organizations using COBOL. COBOL may be outdated but is still solid and serves a purpose. There are third party tools that will help you address SFTP’ing files relatively easily. But, like with anything else, if you don’t already have the solution, just build it. It’s not as complicated as it seems. Don’t worry gentle reader, I got you. With the right setup and a few simple steps, you can get it working smoothly. Granted, I am using the word “simple” loosely, since everything is difficult when you are learning. The good news is that in this guide, I’ll walk you through how to set up an SSIS script task that uses the WinSCP.net assembly to download files over SFTP.

We’ll break this down into four main tasks:

1. Setting up the required directories for our SSIS script.
2. Installing WinSCP and grabbing the necessary files.
3. Setting up a local SFTP server for testing (if you don’t have one already).
4. Creating the SSIS project and writing the script to interact with WinSCP.