ETL: SFTP Source

How to connect to an SFTP server and configure the SFTP source component to read files in your Integrate.io ETL pipeline.

The SFTP source reads files from any Secure File Transfer Protocol (SFTP) server and feeds them into your pipeline. It supports CSV, TSV, JSON, Excel, Parquet, Raw, and compressed formats.

Use case: File Workflow Automation, picking up, transforming and delivering files on a schedule.

Once your connection is set up, see File Storage Options for the full set of read options (file formats, schema, and more) shared across every file storage provider.

Connection Setup

To allow Integrate.io ETL direct access to your server, create firewall rules to allow inbound connections from the Integrate.io IP address list. Alternatively, follow the directions for SSH tunnel or reverse SSH tunnel access.

1. Click the Connections icon (lightning bolt) in the top-left menu.

2. Click New connection.

   
3. Select Secure File Transfer Protocol (SFTP).

   
4. Name the connection and fill in the connection details:

   Field	Description
   Access type	Direct connection (requires firewall rules) or SSH tunnel. See the IP list and firewall guide.
   Hostname	The hostname of the SFTP server.
   Authentication Method	Password or public key. For public key, copy your account’s public key from Settings > Account Settings > Profile and add it to the SFTP user’s authorized_keys file.
   User Name	The username to connect with.
   Password	The password for password authentication. Cannot contain the @ character.
   Port	TCP port of the SFTP server. Default is 22.

   
5. Click Test connection to verify the credentials.

6. Click Create SFTP connection.

   

   The connection appears in the connections list.

   

---

Source Configuration

Connection

Select your SFTP connection from the dropdown, or create a new one using the steps above.

Source Path

The path to the input file, folder, or pattern on the SFTP server. Paths must begin with /. Integrate.io prepends the slash automatically if omitted.

Example	Description
/data/sales/2015/01/log.csv	A single file
/data/sales/2015/01/	All files in a folder
/data/sales/2015/{01,02}/	A pattern matching multiple folders

You can use wildcard characters for pattern globbing. File and directory names beginning with an underscore (_) or a dot (.) are ignored.

SFTP has no bucket or container field. The path is relative to the SFTP server root.

Source File Format

Record delimiter: what breaks the data into records:

• New line (\n, \n\r, \r): each line in the file is one record.
• End of file: each file is treated as a single record.

Record type: the format of each record:

Type	Description
Delimited values	Fields separated by a delimiter (comma, tab, or custom). See Delimited values parameters.
Excel	An .xlsx workbook. See Excel parameters.
JSON object	Each record is a JSON object enclosed in {}. See JSON parameters.
Raw	The entire record is read as a single string or binary field.
Parquet	Reads .parquet files. Columns with DATETIME/INT96 data type are not supported.

Avro file format is not supported.

Source data can be compressed (zip, gzip, or bzip2) or uncompressed. Zip files can be password-protected; enter the password in the Zip Password field. Source data must be UTF-8 encoded. For other encodings, see processing a different encoding.

Case-insensitive path (zip files only): when the source path contains .zip, a Case-insensitive path checkbox appears. Enable it to match the zip file path without regard to letter case.

To read ASC X12 EDI files such as 834 benefit enrollment files, use the dedicated EDI (X12) source instead.

---

Delimited Values Parameters

Applies when record delimiter is New line and record type is Delimited values.

1. Field delimiter: select from the dropdown (comma, tab) or type a single character or escape sequence:

   Escape	Character
   \b	Backspace
   \f	Form feed
   \n	Newline
   \r	Carriage return
   \t	Tab
   \'	Single quote
   \"	Double quote
   \\	Backslash

2. String qualifier: if fields are enclosed in quotes, select ' or ". To allow line breaks inside quoted fields, select " (newline inside) or ' (newline inside). Use the newline-inside option with caution, as unbalanced quotes can affect job performance.

3. Escape character: available when a string qualifier is set. Specifies the character used to escape qualifier characters within field data.

4. First row contains column names: check to skip the header row in each file.

5. Detect header by name: locates the header row dynamically by matching your schema column names. Use this when the header is not always on row 1 (for example, files with a leading title or blank rows before the header). The full schema header must still appear in column order in each file.

---

Excel Parameters

Applies when record type is Excel.

Field	Description
Select sheet by	Name: target a sheet by its tab name. Position: target by position in the workbook (1 = first visible sheet, hidden sheets not counted). Leave both unset to combine all sheets (all sheets must share the same schema).
Sheet Name	Shown in Name mode. Enter the exact tab name.
Sheet Number	Shown in Position mode. Use position mode when the sheet name changes between files but its position stays the same.
Ingest large Excel file	Enables streaming mode for large files. Target a single sheet by name or position, or leave both unset to combine all sheets.
First row contains column names	Skip the header row.
Match columns by name	Maps workbook columns to your schema by header name instead of position. Use when column order varies between files.
Detect header by name	Locates the header row dynamically. Combined with Match columns by name, both the row and column order can vary. Glob patterns (e.g. 2025_*.xlsx) are supported when matching by name.

Password-Protected Workbooks

If a workbook is encrypted with a password (set via Protect Workbook in Excel), check This file is password protected and enter the password in the File Password field. Integrate.io decrypts the workbook during schema preview and job runs. Passwords containing special characters such as $ are supported.

The checkbox appears when the source path ends in .xlsx or .xlsm. Legacy .xls files are not supported for password decryption. Unchecking the box clears any saved password.

Password-protected workbooks do not use streaming mode. When a password is set, enabling Ingest large Excel file reads the workbook in standard mode.

When streaming a single sheet, date cells come through as Excel serial numbers (e.g. 45306). Declare those columns as int or decimal in your schema, or transform them downstream.

---

JSON Parameters

Applies when record type is JSON object.

Base record JSONPath Expression: defines the base record and extracts nested objects or arrays.

Preset	Behavior
Object	Uses the keys of the root JSON object as input fields.
Array	Uses the keys of JSON objects within an array as input fields.
Custom expression	Example: $.data[*]/ extracts fields from objects inside an array named data.

Read more about processing JSON data.

---

Source Action

All four source actions are available for SFTP. When using Move processed files, the Append string to filename option is supported.

Determines what happens to source files during and after processing.

Action	Description
Process all files directly from source	Default. Files are read and processed in place. Available for all providers.
Process only new files (Incremental load)	Reads only files that have not been processed before, tracked via a manifest file. Not supported for Excel. See incremental load.
Copy, merge and process all files	Reads all files, merges them into larger files, then processes. Use for sources with many small files. Fails if the input path points to a single file.
Move processed files to another folder	Moves source files to a backup directory after processing.

Incremental Load (Process Only New Files)

Field	Description
Manifest connection	The connection used to store the manifest file. Defaults to the source connection.
Manifest path	Path to the manifest file, e.g. bucket/path/to/manifest.gz. Created on first run. Each run compares the current file listing to the manifest and processes only new files. Previous manifests are kept as backups.

Move Processed Files

Field	Description
Destination path	The folder to move processed files to.
Append string to filename	Optionally append a custom string (e.g. _processed) to each moved file's name.

---

PGP Decryption

To decrypt source files encrypted with OpenPGP, select an active entry from the PGP Key dropdown. The entry must hold a private key. Integrate.io decrypts each matching file before parsing it.

Paths that contain a space, semicolon, or parenthesis are not supported when a PGP key is set.

See PGP Keys for how to add and manage keys.

---

Source Schema

After defining the source location and format, select the fields to include.

Format	Schema behavior
Delimited values	Fields are read by position. Define all fields in order.
JSON	Define only the fields you want to use. Specify the JSON key for each field.
Raw	Single field containing the entire record.

For each field, define:

• Alias: how to refer to the field in downstream components. Illegal characters are flagged before you close the dialog.
• Data type: the type to cast the field to.
• Key (JSON only): the key name in the source JSON.

You can also add fields from the Meta Data section. They are not read from the file: Integrate.io fills them in for every row. A preview reads only the first file the path matches, so every preview row shows that file's path and time; a job run fills in each row's own file. Change a meta data field's alias to choose its column name. If the file already has a column named file_path or file_modified_at, that column is read like any other and the meta data field of the same name isn't offered.

Field	Type	Value
file_path	string	The path of the file the row was read from.
file_modified_at	datetime	The last-modified time of the file the row was read from, in UTC. Every row from the same file gets the same value.

Use file_modified_at to tell apart deliveries of a file that keeps the same name, or to keep only the newest version of a resent file. It is the time the storage reports for the file, not the time the job ran (for that, use the $_JOB_SUBMISSION_TIMESTAMP variable):

• Amazon S3, Google Cloud Storage, Azure Blob Storage: when the object was last written, usually the upload time.
• SFTP, HDFS: the file's modification time, to the second.
• FTPS: depends on the server. Many FTP servers report times only to the minute, and some in the server's local time zone.
• A file inside a zip archive (a path such as archive.zip/data.csv): the archive's time.

Some transfer tools keep the original file's time when they copy it (for example rsync -t or scp -p). For files delivered that way, the value is when the file was created at the sender, not when it arrived.

file_modified_at is not available for Excel, Parquet, XML, EDI (X12) or whole-file raw sources, for a path ending in .zip, for Google Drive, or with the Process only new files (Incremental load) and Copy, merge and process all files source actions. In those cases the files are copied before they are read, and the copy does not keep the original time.

---

FAQ

How do I allow Integrate.io to connect to my SFTP server?

Add the Integrate.io IP addresses to your server’s firewall allowlist. If direct access is not possible, use an SSH tunnel.

Can I use SSH key authentication instead of a password?

Yes. Select Public key as the authentication method, then copy your Integrate.io account’s public key from Settings > Account Settings > Profile and add it to the SFTP user’s authorized_keys file on the server.

My password contains an @ symbol — what should I do?

Passwords cannot contain @ in the SFTP connection form. Contact your server administrator to change the password, or switch to public key authentication.

Does SFTP support incremental load?

Yes. Select Process only new files (Incremental load) in the source action settings and configure a manifest path. See reading file storage data incrementally.

---

Related

SFTP Destination

File Storage Options

Integrate.io IP Address List

SSH Tunnel Access

Reading File Storage Data Incrementally

SFTPtoGo