Read SFTP / FTP Files in SSIS (CSV, JSON, XML)

Introduction

In our previous blog, we saw how to perform SFTP / FTP File Operations in SSIS. Now in this blog, we will see How to Read SFTP / FTP Files in SSIS (CSV, JSON, XML Format files). To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more. They are Coding free, drag and drop high-performance suite of Custom SSIS Components and SSIS Tasks. If you like to perform file operations on FTP Files (e.g. Download, Upload, Create, Delete) then check these articles.

In nutshell, this post will focus on how to Read files from secure FTP Storage (CSV, JSON and XML Files) and load into SQL Server Table in few clicks.

Components Mentioned in this article

Prerequisite

  1. First, you will need to have SSIS installed
  2. Secondly, make sure to have SSDT
  3. You have obtained FTP/SFTP Credential.
  4. Finally, do not forget to install ZappySys SSIS PowerPack

What is FTP, FTPS, SFTP, FTP/SSL?

Many people still have confusion over various protocols around FTP terminology. Most common terms you hear are as below.

  • FTP
  • Classic FTP
  • Secure FTP
  • SFTP
  • FTPS
  • FTP/SSL
  • FTP over SSL
  • FTP Over SSH

You must be overwhelmed by now? So are they all same or different? See below for quick clarification in nutshell.

Mainly there are 2 protocols for FTP and yes they are very different but still used to do Files Transfer to/from the remote server.

  1. Classic FTP – sometimes referred as just FTP (Typically server runs on Port 21)
  2. SFTP – Sometimes referred as Secure FTP or FTP over SSH (Typically server runs on Port 22)

So in short SFTP is not the same as the Classic FTP protocol. SFTP is more secure because traffic is always encrypted. On the other hand, Classic FTP is not encrypted by default but you can use FTP over SSL to request encrypted traffic in classic FTP. FTP over SSL also referred to as FTPS or FTP/SSL.

Getting Started

In order to start, we will show several examples. ZappySys includes an SSIS Secure FTP Source for CSV/JSON/XML File that will help you in reading CSV, JSON and XML Files from FTP to the Local machine, Upload files(s) to FTP Storage. It will also support Delete, Rename, List, Get Property, Copy, Move, Create, Set Permission … and many more operations. Here we are showing you is, How to download files from FTP Storage.

You can connect to your FTP by entering your FTP credentials.

Read SFTP / FTP Files in SSIS (CSV, JSON, XML)

Let´s start with an example. First of all, In this SSIS FTP Source for CSV/JSON/XML File task example, we will read CSV/JSON/XML files from FTP to SQL Server database.

  1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit
    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

  2. Now drag and drop relevant Secure FTP for CSV/JSON/XML File Task from the SSIS Toolbox.
    Drag and Drop FTP Source

    Drag and Drop FTP

  3. Create a connection for Secure FTP Account.
    Create FTP Connection

    Create FTP Connection

  4. Likewise, select the relevant single file to read from FTP in their relevant source of CSV/JSON/XML File Task.
    Select File From Azure Blob Storage

    Select File From FTP Storage

  5. Similarly, we can also read the multiple files stored in FTP Storage using wildcard pattern supported e.g. dbo.tblNames*.csv / dbo.tblNames*.json / dbo.tblNames*.xml in relevant source task
    Use wildcard pattern .* to read multiple files data

    Use wildcard pattern .* to read multiple files data

  6. We can also read the zip and gzip compressed files also without extracting it in the specific FTP Storage for CSV/JSON/XML File Task.
    Reading zip and gzip compressed files (stream mode)

    Reading zip and gzip compressed files (stream mode)

  7. Finally, we are ready to load this file(s) data into the SQL Server.

Load Secure FTP files data into SQL Server

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
  2. Connect our Source component to OLEDB Destination
  3. Double click OLEDB Destination to configure it
  4. Select Target Connection or click NEW to create new connection
    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
    Create NEW Table SSIS OLEDB Destination

    Create NEW Table SSIS OLEDB Destination

  7. Click OK to Save OLEDB Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

Conclusion

Above all, in this blog, we learned how to Read Secure FTP Storage Files in SSIS. Even more, we used Secure FTP Source for CSV FileSecure FTP Source for JSON File and Secure FTP Source for XML File to read the file(s) from FTP Storage and load data into SQL server. You can download SSIS PowerPack here to try many other scenarios not discussed in this blog along with 70+ other components.

References

Finally, You can use the following links for more information:

Posted in SSIS SFTP / FTP Connection, SSIS SFTP CSV Source, SSIS SFTP JSON Source, SSIS SFTP Task, SSIS SFTP XML Source and tagged , , , , , , , .