SFTP and FTP Data Extraction

Many organizations have data in SFTP and FTP sites that need to be extracted for data analysis purposes. Because of security concerns, we strongly recommend avoiding extraction from unsecured sites such as FTPs.

Many modern and robust SFTP applications (including free ones) provide scripted ways to extract files to a local hard drive.

The following is an example of a process to extract files from SFTP and load them into your data warehouse using WinSCP (a free and open-source SFTP tool):

• Install the latest WinSCP from https://winscp.net/

• Follow WinSCP instructions to connect to your target SFTP site and download the desired files locally for testing purposes. Depending on your SFTP site security, this part of the process might be the most time-consuming.

• To create the scheduled file download, on WinSCP, right-click on the file to download → Download → click the dropdown icon by Transfer settings… → Generate Code → change Format to Batch file → Copy to Clipboard → save the content into a text editor and create your Windows batch file.

  • Double-check that the Windows folders auto-created by this process exist, or change the folders accordingly. Ex.: the script creates a dummy folder to save the WinSCP log file. Create that folder or change it in the batch file.

• Run the created batch file to be sure it’s working as expected. If you have issues, run it on a Command Line session to read the steps and it runs.

• Use ETL+ to complete the process:

  • Map/extract the downloaded file(s).

  • Use a Job to run the batch file and to extract the downloaded file(s).

  • Schedule the Job to run on the device where the WinSCP process was set up.