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.
- Basic knowledge of Treasure Data, including the TD Toolbelt
- Basic knowledge of Salesforce Marketing Cloud
- TD account
On the SFMC dashboard, in your account, select Administration.
From the Account drop-down menu, select FTP Accounts. You are to establish an SFTP account.
In the FTP Accounts pane select Add FTP User.
Provide an FTP account password.
Get your key and IV (initialization vector) value as described in this article : Field-Level Encryption
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.
| 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. |
You need to choose Authentication mode as Password
You need to choose Authentication mode as Public/Private key pair.
After entering the credential information, you must enter a connection name to filter your connections in Choose Saved Connection dialog.
From here, enter your target columns to be encrypted. Key and IV are required to encrypt the columns before sending to SMC SFTP server
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.
| Field | Type | Description |
|---|---|---|
| Encoders | string |
|
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, |
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:
Check the SFMC dashboard to verify a successful import.
You can use CLI for Result Export to SMC SFTP.
In the following example, note the escape newline with backslash in the key.
Example,
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 prestoRefer to the following example:
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 prestoQ: 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.
- 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. 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.
When you decrypt using non-built-in encryption, the file will return to a compressed format such as .gz or .bz2.
When you decrypt using built-in encryption, the file will return to raw data.