Move data from SFTP to SQL Server using Data Integration Platform
Introduction:
This article explains how to move data from the SFTP (Secure File Transfer Protocol) server to the SQL table using the Syncfusion Data Integration Platform.
Steps Involved:
Using the Syncfusion Data Integration Platform:
- To fetch the data from the SFTP server.
- Process the Excel sheet to CSV data.
- Move the final processed data into a SQL database.
The SFTP server has the following Excel sheet in its root directory and you need to move this file using the Syncfusion Data Integration Platform.
Defining a dataflow in the Data Integration Platform as shown in the following image.
Data flow in Data Integration Platform
Step 1: Fetch files from the SFTP server
Drag and drop the GetSFTP processor and specify the following properties:
Hostname: <Specify the hostname or IP address of the remote system in which the SFTP server is running>
Username:<Specify the username to connect to SFTP server>
Password:<Specify the password to connect to SFTP server>
Set the “Search Recursively” property to “true” to search through all the subdirectories of the SFTP root directory.
Set the “Delete Original” property to “false” to keep the files in the SFTP server after file transmitting.
Step 2: Convert the Excel sheet to CSV file
Drag and drop the ConvertExcelToCSV processor and if the Excel file has more than one sheet, specify the sheet name to be converted to CSV. Otherwise, leave it empty.
Step 3: Insert CSV data into a SQL table
Drag the PutDatabaseRecord processor and configure it as follows to move the data into the created SQL table.
Record Reader: <Enable the CSVReader controller service to read the incoming CSV data>
Statement Type: <Select the statement type from the given options>
Database Connection Pooling Service: <Enable the DBCPConnectionPool controller service to connect to SQL database>
Table Name: <Specify the table name to insert data>
Configure the CSVReader controller service as follows:
Enable all the created controller services as follows:
Make sure you have created a SQL table (healthdata1) using the following query and create a controller service for it in the Data Integration Platform. For more details on controller settings, refer to our documentation.
Inserted CSV data in the SQL table:
