# Salesforce Marketing Cloud Export Integration Using SFTP

You can send job results directly to your Salesforce Marketing Cloud (SFMC) SFTP server.

This feature is in BETA version. For more information, contact your Customer Success Representative.

## Prerequisites

- Basic knowledge of Treasure Data, including the [TD Toolbelt](https://toolbelt.treasuredata.com/)
- Basic knowledge of Salesforce Marketing Cloud
- TD account


## SMC SFTP Account

On the SFMC dashboard, in your account, select **Administration**.

![](/assets/salesforce-admin1.eff935cefb395b606aeb602a0100eb6df485b8000cd50e06061add703ef6929a.b90f87c9.png)

From the Account drop-down menu, select **FTP Accounts**. You are to establish an SFTP account.

![](/assets/salesforce-ftpacct1.66adda154fad3d9777d81bf78d545cdff27a9b96adcc03d5ed2f7332fa91d165.b90f87c9.png)

In the FTP Accounts pane select **Add FTP User**.

![](/assets/salesforce-ftpacct2.b0528d5777b879eabdd4d741ef76fe4e0dcc3c81f074704ef8b2ece9e49a3820.b90f87c9.png)

Provide an FTP account password.

![](/assets/salesforce-ftpacct3.18166e62986ed047a31a9dac4e446e6e53a92e578eca2337cb60d7644fdcfd52.b90f87c9.png)

## SMC Key and IV

Get your key and IV (initialization vector) value as described in this article : [Field-Level Encryption](https://help.salesforce.com/articleView?id=mc_overview_implement_field_level_encryption.htm)

### Basic Usage

In TD Console, navigate to Queries and select **New Query** to access the query editor. You can pick the query language and write your query. Select **Output Results**.

![](/assets/smc_sftp_result_outputs.6a2c98a3a74720eb951a1d28e99cf35487af32a2a6cfb4194f4575668e93e84a.b90f87c9.png)

### Create or Select an Existing SMC SFTP Connection

| Option | Task Steps | Tip |
|  --- | --- | --- |
| Create a connector | - Go to Integrations Hub > Catalog - Select the Salesforce Marketing Cloud via SFTP icon - Complete the required fields on the first pane.   Next, you complete the Configuration fields. | Sometimes you need to define the column mapping before writing the query. |
| Select a connector | - Go to TD Console - Navigate to the query editor.  - Access the query that you plan to use to export data. - Select Output Results. The Choose Save Connection dialog opens. - Type the connection name in the search box to filter and select the connection that you want.   Next, you complete the Configuration fields. | You can also create a new connection from here by selecting Create New Connection. |


## Creating a New Connection

### Specifying a User Name and Password

You need to choose Authentication mode as Password

![](/assets/create_new_smc_sftp_con.035d953826da388bf84511b92a16fe7a80749a27115e8c9c970c28fea02a09fc.b90f87c9.png)

### Specifying a Secret Key File

You need to choose Authentication mode as Public/Private key pair.

![](/assets/create_new_smc_sdtp_con1.3c6cf651aff7eaaf7650183a6dbe240c3823ab5e3d39f1e6e42f2abc9244c051.b90f87c9.png)

After entering the credential information, you must enter a connection name to filter your connections in Choose Saved Connection dialog.

![](/assets/smc_sftp_connection_name.0df6f79c7796046f0ce7928b62b459dbdd76531d8af1ba709821c0a36c3be1f6.b90f87c9.png)

## Selecting an Existing Connection

![](/assets/smc_sftp_connection.85e7fe13d8586ad17a83907a384bfde0f9b8f302f111d7bd330045fde9e7b501.b90f87c9.png)

## Complete the Configuration Fields

From here, enter your target columns to be encrypted. Key and IV are required to encrypt the columns before sending to SMC SFTP server

![](/assets/smc_sftp_configuration.101dc17e8a96ba7e32b68b6e3dc9e20ed0dd7f16fb383ede6659cb76cbeba483.b90f87c9.png)

After you complete the configuration dialog, if you want to edit and update any fields in the configuration dialog, you must re-enter your key and IV to send encrypted data to SFMC.

If you don't want to encrypt data sent to SMC SFTP, leave encryption columns, key, IV, blank.

## Encryption Data

| Field  | Type  | Description |
|  --- | --- | --- |
| Encoders | string | - `None`: no encoder applied (default)
- `GZ`: compresses the file with gzip before upload
- `BZIP2`: compresses the file with bzip2 before upload
- `PGP Encryption`: encrypts the file with the provided public key before upload

 |
| Public Key (required when Encoder is `PGP Encryption`) | string | Public key used to encrypt the file before upload. |
| Key Identifier (required when Encoder is `PGP Encryption`) | string | Key ID of the encryption subkey used to secure the file; the master key is excluded from the encryption process. |
| Armor (optional) | checkbox | Enables ASCII armor for the encrypted file. |
| Compression Type (optional) | string | Compression algorithm applied prior to encryption for uploading to the SFTP server.Compress the file before encrypting and uploading. After decryption, the file returns to its original compressed format (for example, `.gz` or `.bz2`).
 |


### Execute the Query

You can either save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified Container destination.

After the job finishes, you can check the output file on SMC SFTP server by using the general SFTP command, as shown in the following example:

![](/assets/salesforce-outputfile.82a2e9a1afd1180e6081dccfb4440db89906dfa70366a457fcbceccaeefb8b32.b90f87c9.png)

Check the SFMC dashboard to verify a successful import.

## Usage from the CLI

You can use CLI for Result Export to SMC SFTP.

### Using a Secret Key File

In the following example, note the escape newline with backslash in the key.

Example,


```bash
td query \
--result '{"type":"smc_sftp","host":"xx.xx.xx.xx","port":22,"username":"xxxx","secret_key_file":"{\"content\":\"-----BEGIN RSA PRIVATE KEY-----\nABCDEFJ\nABCDEFJ\n-----END RSA PRIVATE KEY-----\"}","secret_key_passphrase":"xxxxxx", "user_directory_is_root":true,"path_prefix":"/path/to/file.csv","sequence_format":"","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF","encryption_column_names":"name, email", "encryption_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", "encryption_iv":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}' \
-d sample_datasets \
"SELECT id, name, email FROM (\ VALUES (1,'a','a@gmail.com’),\ (2,'b','b@gmail.com’),\ (3,'c','c@gmail.com’)\ ) tbl (id, name, email)" \
-T presto
```

### Using a User Name and Password

Refer to the following example:


```bash
td query --result \
'{"type":"smc_sftp","host":"xx.xx.xx.xx","port":22,"auth_method":"Password","username":"xxxx","password":"xxxxx","user_directory_is_root":true,"path_prefix":"/path/to/file.csv","sequence_format":"","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF","encryption_column_names":"name, email", "encryption_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", "encryption_iv":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}' \
-d sample_datasets \
"SELECT id, name, email FROM (VALUES (1,'a','a@gmail.com’),(2,'b','b@gmail.com’),(3,'c','c@gmail.com’)) tbl (id, name, email)" -T presto
```

## References

- [The Embulk-encoder-Encryption document](/int/embulk-encoder-encryption-pgp)


## FAQ for the SMC_SFTP Data Connector

Q: I cannot connect to my SFTP server. What can I do?

A: Here are some things to check:

- Verify that your protocol is valid. If you intended to use *SFTP*, you can use this integration. If you intended to use *FTP*/*FTPS*, try to connect with the [FTP Server Import Integration](/int/ftp-server-import-integration).
- If you are using a firewall, check your accepted IP range and port. Server administrators sometimes change the default port number from TCP 22 for security reasons.
- Be sure that your private key has an *OpenSSH* format. Treasure Data does not support other formats like “PuTTY”.
- Treasure Data does not support the default format of the private key since [OpenSSH 7.8](https://www.openssh.com/releasenotes.md). Re-generate the key using '-m PEM' option.


Q: I cannot find my uploaded file on the SFTP server. What happened?

A: If your SFTP server does not support Append mode, the integration may not be able to update the file with the error "SFTP Exception: no such file."  For stability reasons, the integration uploads the file content in batches. Consequently, you will need to enable the Append mode on your SFTP server. If Append mode cannot be enabled on your SFTP server, you will need to set temp_file_threshold to 0 to bypass this feature.

Note: Please ensure that you compress your file before encrypting and uploading.

1. When you decrypt using non-built-in encryption, the file will return to a compressed format such as .gz or .bz2.
2. When you decrypt using built-in encryption, the file will return to raw data.