SFTP Adapter

Overview

When you use the SFTP adapter as a source, you can read data from a file as well as fetch a list of files present in any directory on an SFTP server location. When you use the SFTP adapter as a target, you can send data to a specified file on the SFTP server. You can either:

Create a new file.
Overwrite an existing file.
Append data to an existing file.

If you have configured the adapter to overwrite a file or append data to a file that does not exist—but if the directory structure in the file path is valid—the file is automatically created. If the directory structure in the specified file path does not exist, the write operation fails unless you have configured the adapter to create a new directory in case the specified file path does not exist. In that case, the adapter automatically creates the missing directories.

Introduction

The SFTP is a protocol for secure file data transfer over computer networks. The SFTP adapter conforms to this protocol and can be configured to act as an SFTP client to transfer and access file data to and from SFTP servers.

Command alias

Specify adapter commands by using a command string on the command line or by creating a command file that contains adapter commands. The command syntax is:

-IA[alias]card_num

-OA[alias]card_num

In the command syntax, -IA is the Input Source Override execution command and -OA is the Output Target Override execution command, alias is the adapter alias, and card_num is the number of the map card. The SFTP adapter alias and corresponding execution commands are listed in the following table.


Adapter	Alias	As Input	As Output
SFTP	SFTP	-IASFTP	-OASFTP

Authenticating connections

The Secure File Transfer Protocol (SFTP) is a protocol for secure file data transfer over computer networks. The SFTP adapter conforms to this protocol and can be configured to act as an SFTP client to transfer and access file data to and from SFTP servers.

The SFTP adapter offers the following two methods to authenticate a user to connect to an SFTP server:

Password-based – You need to provide the correct password.
Keypair-based – You need to enter the private key file path, public key file path, keypair name, and passphrase.

You can select any one of the two authentication methods as the preferred one. The adapter first attempts the preferred authentication method. If that attempt is not successful, the adapter then tries for the other method to authenticate.

Note: For Keypair-based authentication method, only the private-key file path is required. The public-key file path, keypair name, and passphrase are optional. Username is required for both the authentication methods.

After successfully connecting to an SFTP server, you can send and receive files to and from the server.

SFTP adapter as a source

SFTP adapter as a source supports:

File list – Use this mode to fetch all the files from any directory on an SFTP server location.
File data – Use this mode to fetch the content of a specified file from an SFTP server location.

SFTP adapter as a target

SFTP adapter as a target supports:

Create – Use this mode to create a new file in the SFTP server location and write data to it, or report an error if the file already exists.
Overwrite – Use this mode to overwrite a file with any new data if the file already exists; or otherwise to create a file and write data to it—if the specified file does not exist.
Append – Use this mode to append any new data to an existing file on the SFTP server location, or otherwise to create a new file and write data to it—if no file of the specified name exists.

Note: When you fetch a file, the file is left on the server or removed based on the value specified in the File Success Action property.

Adapter properties and commands

This section lists the properties supported by the adapter.

Host

Specifies the host name or IP address of the SFTP server. This is a mandatory property. The corresponding adapter command is -HOSTname (or -HOSTNAMEname).

Port

Specifies the port on which the SFTP server is listening for incoming connections. The default value is 22. The corresponding adapter command is -PORT number (or -PORTNUMBER number).

Preferred authentication

Specifies the authentication method to try first when establishing a connection. The supported values are Password and Public Key. The default value is Password. The corresponding adapter command is -PA method (or -PREFERREDAUTHENTICATION method) where the method is one of password or public key.

User

Specifies the username for the connection. This is a mandatory property. The corresponding adapter command is -USR name (or -USERNAME name).

Password

Specifies the password value to use when performing password-based authentication. The corresponding adapter command is -PWD value (or -PASSWORD value).

Known hosts file path

Specifies the location of the known hosts file. The value is optional, and when specified results in enforcing strict host name checking against the specified file when establishing connections. If not specified, it results in omitting strict host name-checking. The corresponding adapter command is -KHFP path (or -KNOWNHOSTSFILEPATH path).

Key pair name

Specifies the name of the keypair to use when performing keypair based authentication. The value is optional and when not specified the name identifying the key pair is derived from the private key file name. The corresponding adapter command is -KPN name (or -KEYPAIRNAME name).

Private key file path

Specifies the location of the private key file to use when performing keypair based authentication. The corresponding adapter command is -PRKFP path (or -PRIVATEKEYFILEPATH path).

Passphrase

Specifies the passphrase is used to access the key data when performing keypair based authentication. The value is optional, and when not specified, it is assumed that the key is not passphrase protected. The corresponding adapter command is -PSP value (or -PASSPHRASE value).

Connection timeout

Specifies the amount of time, in seconds, to wait for the server to respond when establishing a connection, before considering the connection attempt unsuccessful. The default value is 30. The value 0 indicates no timeout. The corresponding adapter command is -CT duration (or -CONNECTIONTIMEOUT duration).

Retry connection count

Specifies the number of times to retry establishing a connection to the server before giving up and reporting the error. The default value is 0, which indicates no retries. This setting is not applicable when performing Test connection operation in the design UI, and the value 0 is automatically enforced in that case. The corresponding adapter command is -RCC count (or -RETRYCONNECTIONCOUNTcount).

Retry connection interval

Specifies the amount of time, in seconds, to wait between successive connection retries. The default value is 60. This setting is applicable only when the Retry Connection Count is set to a value greater than 0.

The corresponding adapter command is -RCI duration (or -RETRYCONNECTIONINTERVAL duration).

Path

Specifies the path of the directory or file to access on the SFTP server. Forward slash (/) character is used as a file separator. If the specified path value starts with a forward slash, it is treated as an absolute path; otherwise, it is treated as a path relative to the current default directory for the connection. The corresponding adapter command is -P path or (-PATH path).

Create directory

Specifies the setting which when enabled results in the automatic creation of all missing directories in the specified directory path. When not enabled, the adapter reports an error if the specified directory structure does not exist on the SFTP server. The setting is applicable only when the adapter is used as a target. The corresponding adapter command is -CD (or - CREATEDIRECTORY).

Read mode

This property specifies the mode in which to use the adapter as a source. The File Data mode is used to read the content of the file specified in the Path setting. The File List mode is used to retrieve the list of absolute paths of the files located in the directory specified in the Path setting. The default value is File Data. The corresponding adapter command is -RM mode (or -READMODE mode), where mode is one of file_data or file_list.

Write mode

Specifies the mode in which to use the adapter as a source. The File Data mode is used to read the content of the file specified in the Path setting. The File List mode is used to retrieve the list of absolute paths of the files located in the directory specified in the Path setting. The default value is File Data. The corresponding adapter command is -WM mode (or -WRITEMODE mode), where mode is one of create, overwrite or append.

File Success Action

Specifies the success action to be taken by the adapter on the file as what to do with it after the successful run of a transaction. The default value is Keep.

The corresponding adapter command is -FILESUCCESSACTION (or -FSA). The following actions are supported:

Keep: Keeps the file at the source after the successful run of a transaction. The corresponding adapter command value is keep.
Delete Always: Deletes the file at the source in case of successful run of a transaction. The corresponding adapter command value is delete_always.
Delete If Empty: Deletes the file at the source in case of a successful run. Checks, if the file is empty or not. If the file is empty, it is deleted, otherwise it is left in place. The corresponding adapter command value is delete_if_empty.

Failure Action

Specifies the failure action to be taken by the adapter on the file as where to place it after the failure run of a map. The default value is Commit.

The following actions are supported:

Commit: This option will commit the transaction in case of transaction failure.
Rollback: This option will rollback the transaction in case of transaction failure.

The following list shows the outcomes for different combinations of File Success Action and Failure Action property values:

keep + commit = keep
delete always + commit = delete
delete if empty + commit (when file has data) = keep
delete if empty + commit (when file does not have data) = delete
keep + rollback = keep
delete + rollback = keep
delete if empty + rollback (when file has data) = keep
delete if empty + rollback (when file does not have data) = keep

Logging

This property specifies the level of logging to use for the log (trace) file produced by the adapter. The default is Off. The value Information means log informational, the value Errors Only means log error messages only, and the value Verbose means log debug and trace level messages along with the informational and error messages.

The corresponding adapter command is:

-T [E|V] [+] [file_path]

-T -> Log adapter informational messages.

-TE -> Log only adapter errors.

-TV -> Use verbose (debug) logging. The log file records all activity that occurs while the adapter is producing or consuming messages.

+ -> Appends the trace information to the existing log file. Omit this argument to create a new log file.

file_path -> The full path to the adapter trace log. If you omit this keyword, the adapter creates the m4sftp.mtr log file in the map directory.

Append log

Flag indicating what to do if the specified log file already exists. When set to true, the log messages are appended to the file. When set to false, the file is truncated, and the messages are written to the empty file. The default value is true.

Log file path

Specifies the location of the log file to which to write log messages. If not specified, the default log file name m4sftp.mtr is used, and the file is stored to the directory in which the executed compiled map resides.

Convert Data

Specifies to convert data to parsed form. if enabled, support for converting CSV, Avro, Excel, Parquet and JSON documents into sets of data records in the CSV format. This means that the data produced by the converters can be loaded directly into relational databases.

Data Format

Specifies the data format. This is a mandatory property. Select one of the following as per the data format type. This property field is applicable when Convert Data property toggle is enabled.

CSV
Excel
Avro
Parquet
JSON

Header

If enabled, specifies whether the CSV data contains a header row.

Quote

Specifies the quote character. Default value is ".

Delimiter

Specifies the field delimiter character. Default value is ,.

Escape

Specifies the escape character.

Separator

Specifies the row separator characters. Default value is \r\n.

Character Set

Specifies the character set of the data. Default value is UTF-8.

Limit

Specifies the maximum number of records to return.

Sample Size

Specifies the number of records to analyze when determining the data structure. Default value is 100.

Worksheet Index

Specifies the index of the worksheet. The index of the first worksheet is 1 (default).

Select Source Tables

Select which arrays of objects in the AVRO data you wish to map to tables in the target.

Find Array

If enabled, specifies that the output will iterate on the first array found in the JSON document.

Array Path

Specifies the path of the JSON array within the document. Omit if the document is an array. For example,"/resources".

Examples

Examples of the GET map function

In the following example, assume that the adapter is used in a GET function.

When the adapter is used to fetch file content:

GET("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -RM file_data -TV /tmp/logfile.log")

When the adapter is used to fetch file list:

GET("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/ -RM file_list -TV /tmp/logfile.log")

When the adapter is used to File Success Action:

GET("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -RM file_data -FSA keep -TV/tmp/logfile.log")

Examples of the PUT map function

In the following example, assume that the adapter is used in a PUT function.

When the adapter is used to create a new file, if the file is not present:

PUT ("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -WM create -TV /tmp/logfile.log", in_data_type)

When the adapter is used to overwrite an existing file:

PUT ("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -WM overwrite -TV /tmp/logfile.log", in_data_type)

When the adapter is used to append data to an existing file:

PUT ("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -WM append -TV /tmp/logfile.log", in_data_type)

When the adapter is requested to automatically create missing directories:

PUT ("SFTP", "-USR testUser -HOST host1 -PORT 22 -PA Password -PWD pwd1 -P /myDirectory/MyFile.txt -WM create -CD -TV /tmp/logfile.log", in_data_type)

Example of RUN() function in a map rule

Here is an example of using RUN() function in a map rule to run a map map1 and override its output card 1 to use SFTP adapter:
```
=RUN("map1", "-OASFTP1 '-HOST server1 -PORT 10022 -USR user1 -PWD password1 -PATH /tmp/test.txt -WM overwrite -TV'")
```
Here is the same example, but this time using command server from command line on Linux to run the map map1.lnx and perform the same override:
```
dtxcmdsv map1.lnx "-OASFTP1 '-HOST server1 -PORT 10022 -USR user1 -PWD password1 -PATH /tmp/test.txt -WM overwrite -TV'"
```