Responsys snapshot

A high-level look at Stitch's Responsys integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release Status

Open Beta

Supported By

Stitch

Stitch Plan

Paid

Supported Versions

n/a

Singer GitHub Repository

Responsys Repository

CONNECTION METHODS
SSH Connections

Supported

SSL Connections

Unsupported

REPLICATION SETTINGS
Anchor Scheduling

Supported

Table-level Reset

Unsupported

Configurable Replication Methods

Unsupported

REPLICATION METHODS
Log-based Replication

Unsupported

Key-based Replication

Supported

Full Table Replication

Unsupported

DATA SELECTION
Table Selection

Supported

Column Selection

Unsupported

View Replication

Unsupported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting Responsys

Responsys setup requirements

To set up Responsys in Stitch, you need:

  • A paid Stitch plan. While those currently in the Free Trial will also be able to set up Responsys, replication will be paused until a paid plan is selected after the trial ends.
  • Export files that adhere to Stitch’s requirements. Files that aren’t set up correctly may not be replicated successfully, or may cause issues during extraction.

Step 1: Add Stitch's Public Key to your Responsys SFTP server

Stitch uses an SSH tunnel to securely connect to your Responsys SFTP server. This means that to connect successfully, you’ll need to add Stitch’s Public Key to your server.

Step 1.1: Retrieve the Stitch Public Key

  1. On the Stitch Dashboard page, click the Add Integration button.
  2. Click the Responsys icon.
  3. The Stitch Public Key will be at the top of the page that opens:

    The Stitch Public Key in the Responsys settings page

Step 1.2: Determine your SFTP server hosting

Next, you’ll need to add the Public Key to your SFTP server. The steps for doing this depend on how your SFTP server is hosted:

Contact Oracle and provide them with the Public Key to complete this step.

If your SFTP server is self-hosted, or not hosted by Oracle, you’ll need to add the Public Key to the authorized_keys file. This will allow Stitch to conntect to the server using a trusted user.

In this step, you’ll create a dedicated SSH user for Stitch and then import the Stitch Public Key into the authorized_keys file.

  1. Run the following commands as root on your Linux server to create a user named stitch:

    adduser --disabled-password stitch
    mkdir /home/stitch/.ssh
    
  2. Next, import the Public Key into authorized_keys, replacing [PASTE KEY HERE] with the Stitch Public Key:

    echo "[PASTE KEY HERE]" >> /home/[stitch_username]/.ssh/authorized_keys
    
  3. Alter the permissions on the /home/stitch directory to allow access via SSH:

    chown -R [stitch_username]:stitch /home/stitch
    chmod -R 700 /home/stitch/.ssh
    

If you skip this step or the Public Key is added incorrectly, the following error will surface when you save the integration in Stitch:

"Message from SFTP server: Authentication failed. - Please ensure that the server is configured to accept the public key for this integration."

Step 2: Verify Responsys Connect data export configuration

Connect Data Export Destination Specification page

Stitch’s Responsys integration replicates .csv or .txt files created as part of a Connect data export job. In the Responsys app, jobs are created and managed in the Manage Connect page, accessed via Data > Connect.

File settings are defined in the Destination Specification step of the Connect data export process, which can be seen in the image to the right.

The settings in the Destination Specification page must match the required values listed in the table below. This will ensure that data is replicated successfully from Responsys.

Setting in Responsys Required value
File to be created

We recommend using a date/time prefix OR suffix for file names, which will result in Responsys creating multiple files.

If a prefix or suffix isn’t used, the export will only ever create a single file. Every new export will replace the entire file, overwriting the data it contains. If this occurs before Stitch runs an extraction job, the overwritten data would be lost and unable to be replicated.

File extension

.csv or .txt

Stitch’s Responsys integration doesn’t currently support replicating other file types.

Character set

Unicode (UTF-8)

File delimiter

Comma

Stitch’s Responsys integration doesn’t currently support other file delimiters.

Field enclosure

Double quotes (")

Insert column header as first line

Box must be checked

Stitch’s Responsys integration requires that the first row of every export file be a column header.

Encryption/compression

Do not encrypt or compress file

Stitch’s Responsys does not currently support replicating encrypted or compressed files.

Additional ready file at completion of download

Select Create file with record count.

In the File extension field, enter ready

Step 3: Add Responsys as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Responsys icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Responsys” would create a schema called stitch_responsys in the destination. Note: Schema names cannot be changed after you save the integration.

The info you need to complete the remaining fields depends on how your SFTP server is hosted:

If your server is hosted by Oracle, you can find its connection details in the Responsys app by navigating to Data > Connect > Destination Connectivity. If you have multiple file locations, click the one you want to connect to Stitch.

Oracle-hosted SFTP server connection details

The image on the right shows an example of the connection details in Responsys.

Fill in the remaining fields in Stitch as follows:

  • Host: In Stitch, enter the value from the Responsys Server field. This will likely be files.responsys.net
  • Port: Leave this as 22.
  • Username: In Stitch, enter the value from the Responsys User name field. In the example, this is demo_scp
  • Path: In Stitch, enter the value of the Responsys Directory path field, or the file server path where completed Responsys export files are stored. In the example, this is download

If your server is hosted by Oracle, you can find its connection details in the Responsys app by navigating to Data > Connect > Destination Connectivity. If you have multiple file locations, click the one you want to connect to Stitch.

Self-hosted SFTP server connection details

The image on the right shows an example of the connection details in Responsys.

Fill in the remaining fields in Stitch as follows:

  • Host: Enter the host address (endpoint) used by the SFTP server.
  • Port: Enter the SSH port used by the SFTP server. This is usually 22.
  • Username: Enter the name of the user you created in Step 1.2. In the example, this is stitch_user
  • Path: Enter the file server path where completed Responsys export files are stored.

Step 4: Define the historical sync

The Sync Historical Data setting will define the starting date for your Responsys integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Responsys’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 5: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Stitch offers two methods of creating a replication schedule:

  • Replication Frequency: This method requires selecting the interval you want replication to run for the integration. Start times of replication jobs are based on the start time and duration of the previous job. Refer to the Replication Frequency documentation for more information and examples.
  • Anchor scheduling: Based on the Replication Frequency, or interval, you select, this method “anchors” the start times of this integration’s replication jobs to a time you select to create a predictable schedule. Anchor scheduling is a combination of the Anchor Time and Replication Frequency settings, which must both be defined to use this method. Additionally, note that:

    • A Replication Frequency of at least one hour is required to use anchor scheduling.
    • An initial replication job may not begin immediately after saving the integration, depending on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

To help prevent overages, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 6: Set tables to replicate

To complete the setup, you’ll need to select the tables you want to replicate to your data warehouse.

Check out the Schema section to learn more about the available tables in Responsys and how they replicate.

  1. In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.

  3. Repeat this process for all the tables you want to replicate.
  4. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.

Initial and historical replication jobs

After you finish setting up Responsys, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Responsys Replication

While data from Responsys integrations is replicated using Key-based Incremental Replication, the behavior for this integration differs subtly from other integrations.

The table below compares Key-based Incremental Replication and Replication Key behavior for Responsys to that of other integrations.

Responsys Other integrations
What's used as a Replication Key?

The time a file is modified.

A column or columns in a table.

Are Replication Keys inclusive?

No. Only files with a modification timestamp value greater than the last saved bookmark are replicated.

Yes. Rows with a Replication Key value greater than or equal to the last saved bookmark are replicated.

What's replicated during a replication job?

The entire contents of a modified file.

Only new or updated rows in a table.


Responsys Schema

Responsys tables will contain the same columns that the source files contains. These tables will also contain common system columns, as well as columns specific to Responsys integrations:

_sdc_source_file

The file path on the SFTP server of the table’s source file.

_sdc_source_lineno

The row number for the record in the source file.



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.