SFTP Integrations

SFTP (Secure File Transfer Protocol) is a network protocol used to securely transfer files over a network. This protocol uses encryption techniques to protect the confidentiality and integrity of the data during the transfer.

Execution of SFTP

The way this protocol is executed in data integration is as follows:

  1. The file is detected and added to the client’s processing queue, concatenating its ID.

  2. When it is its turn —there are several processors scanning each client’s queue—, all lines of the file are read and the reading format is checked.

  3. Records with invalid values (the product does not exist, the date is incorrect, the quantity is negative, etc.) are filtered out, and lines that cannot be processed —those containing errors— are ignored. For all other values processed as correct:

    • The current records are deleted.

    • The new records are inserted.

The saving process is asynchronous, meaning multiple values are executed simultaneously. Generally, the process does not stop due to the number of errors, only certain types of errors that may endanger the system such as timeout (database overwhelmed) or database insertion errors, but not format errors.

Measures integration

Once the SFTP directory is created for the client, and after receiving all necessary connection information (credentials, host, etc.), files can be uploaded according to the agreed frequency. The client deposits the CSV files in their SFTP directory for processing as detailed here.

In the column headers, it is necessary to specify:

  • STORE: external service identifier.

  • DATE: date, by default in yyyyMMdd format.

  • TIME: local time, by default in HHmm format.

  • ZONE: external product or section identifier.

  • The various measures configured in the business: the name must exactly match the one configured in Orquest.

Below is an example:

STORE DATE TIME ZONE measure1 measure2 measure3

serviceId

20241124

0000

productId

0.77

2

1.5

serviceId

20241124

0015

productId

6.66

1

6.4

serviceId

20241124

0030

productId

7.93

7

1

serviceId

20241124

0045

productId

5.87

10

1.4

serviceId

20241124

0100

productId

8.09

6

8.7

The format of these columns may vary depending on the business configuration set in the system. This means that the structure and content of the columns will depend on the parameters and options set. For example, the date format could be configured by adding dashes: yyyy-MM-dd.

Through this method, files containing the real measures are securely sent to the Orquest SFTP server, from where they are processed to integrate the data into the system.

Considerations

  • The correct processing of a file does not imply data insertion into the system: if all lines contain errors and are ignored, the file is processed correctly but there are no valid data to add.

  • File processing time will depend on the number of elements in the queue: it will take longer if it coincides with another upload or if many files are loaded at the same time.

  • There are different SFTP templates for each business: they are generated based on the parameters configured by the business.

  • It is recommended not to send files with the same name, in order to better monitor data processing.

Common errors

Below are some of the most common errors in file processing via SFTP.

SFTP Error Description

encoding_possibly_wrong

The processor detects that the file is not encoded as UTF-8.

invalid_date_format

Error in date and time format.

product_not_exist

The product does not exist in the business or is not configured.

product_for_service_not_exist

The product for the service does not exist in the business or is not configured.

invalid_value

The file contains invalid values, such as negative values.

demandtype_not_exist

The type of measure does not exist in the business.