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
 |
Secure FTP Source for JSON File |
 |
Secure FTP Source for XML File |
 |
Secure FTP Source for CSV File |
 |
Secure FTP Destination for CSV File |
Prerequisite
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- You have obtained FTP/SFTP Credential.
- 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.
- Classic FTP – sometimes referred as just FTP (Typically server runs on Port 21)
- 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.
- 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
- Now drag and drop relevant Secure FTP for CSV/JSON/XML File Task from the SSIS Toolbox.
Drag and Drop FTP
- Create a connection for Secure FTP Account.
Create FTP Connection
- Likewise, select the relevant single file to read from FTP in their relevant source of CSV/JSON/XML File Task.
Select File From FTP Storage
- 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
- 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)
- Finally, we are ready to load this file(s) data into the SQL Server.
Load Secure FTP files data into SQL Server
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS - Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
SSIS Upsert Destination - Columns Mappings - Click OK to Save Upsert Destination Settings
- 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
- 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 File, Secure 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:
