Support syncing multiple files incrementally from SFTP

[Phlair]

Tell us about the problem you're trying to solve

It's currently possible to sync a single file from SFTP using this connector but we should enhance SFTP support to handle multiple files and incremental loading a la S3 source.

Describe the solution you’d like

Use the abstract files source to build a SFTP connector.

Additional context

This is blocked by https://github.com/airbytehq/airbyte/issues/5111

[kkarski]

Also very interested in this connector as a source. Looking for

• Pull all files in a folder
• Pull all files matching a file pattern
• Exclude all files matching a pattern
• Don't pull a files already downloaded (unless force download)
• Ability to use ssh keys or certificates as auth method
• Dynamic naming of both source / destination path i.e. /${year}/${month}/${day}/
• Ability to rename source file on download
• Mutual auth, i.e. client presents cert to server
• Download files to an s3 bucket, not just load to DB
• Support raw download of json, csv, gzip, zip, avro, parquet, xml, etc

[yuhuishi-convect]

Have a very similar use case, but with S3 storage. Need to sync multiple non-heterogeneous files with different schemas into multiple destination tables. The current workaround is to create multiple file sources that handle each individual file independently. But this is not quite ideal since the files form a "bundle" naturally and should be done in one task.

[renatodossantosleal]

Hi! Is this on roadmap? I've got a similar use case for it.