Page tree
Skip to end of metadata
Go to start of metadata

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

Currently this data connector is in Beta. For more information, contact support@treasuredata.com.




Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt

  • Basic knowledge of Salesforce Marketing Cloud

  • TD account

SMC SFTP 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.


SMC Key and IV

Get your key and IV (initialization vector) value as described in this article : Field-Level Encryption

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.

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

Specifying a Secret Key File

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.


Selecting an Existing Connection

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

 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 sending to SMC SFTP, leave encryption columns, key, IV, blank.

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:


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,

1
2
3
4
5
6
7
8

$ 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
 
Job xxxxxxxx is queued.
Use 'td job:show xxxxxxxx' to show the status.


Using a User Name and Password

Refer to the following example:

1
2
3
4
5
6
7
8

$ 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
 
Job xxxxxxxx is queued.
Use 'td job:show xxxxxxxx' to show the status.

  • No labels