Salesforce Marketing Cloud Connection Details

Introduction

Connector Version

This documentation is based on version 21.0.8662 of the connector.

Get Started

Salesforce Marketing Cloud Version Support

Establish a Connection

Authenticate to the Salesforce Marketing Cloud REST API

To connect, set Schema to "REST". The Salesforce Marketing Cloud REST API uses the OAuth authentication standard. To authenticate using OAuth, you will need to create an app to obtain the OAuthClientId and OAuthClientSecret connection properties. See Using OAuth Authentication for an authentication guide.

Authenticate to the Salesforce Marketing Cloud SOAP API

The Salesforce Marketing Cloud SOAP API can connect using either your login credentials, or Using OAuth Authentication.

To connect to data using basic (login credentials) authentication, set the following:

• User: The Salesforce Marketing Cloud user account used to authenticate.
• Password: The password used to authenticate the user.
• AuthScheme: Set this to Basic.
• UseLegacyAuthentication: Set this to True.

to your login credentials.

Connect to the Salesforce Marketing Cloud APIs

Once you have authenticated to either the SOAP or REST API, you are ready to connect to data.

You may optionally refine data access using the following properties:

• Instance: The instance of the Salesforce Marketing Cloud API used. The default Instance is s7 of the Web Services API; however, you can use this property to specify a different instance.

Use OAuth Authentication

OAuth requires the authenticating user to interact with Salesforce Marketing Cloud using the browser. The connector facilitates this in various ways, as described below.

Custom Credentials

You will need to register an app to obtain the OAuthClientId and OAuthClientSecret.

Create a Custom OAuth App

See Creating a Custom OAuth App for the procedure.

Custom Credentials

Desktop Authentication with your OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Get an OAuth Access Token

After setting the following, you are ready to connect:

• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• APIIntegrationType: Set this to either Web-App or Server-to-Server depending on which you selected when creating your app.
• OAuthGrantType: Alternatively, instead of setting APIIntegrationType, the OAuthGrantType may be set directly to either Code (Web-App) or Client (Server-to-Server). It may be desirable to use OAuthGrantType in cases where muliple produts are being used to keep things consistent.
• CallbackURL: Set this to the Redirect URL in your app settings.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken. .
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Headless Machines

Use OAuth on a Headless Machine

To create Salesforce Marketing Cloud data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the connector to automatically refresh the access token from the headless machine.

Use the Credentials for a Custom OAuth App

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Obtain a Verifier Code

Set the following properties on the headless machine:

• InitiateOAuth: Set this to OFF.
• OAuthClientId: Set this to the App ID in your app settings.
• OAuthClientSecret: Set this to the App Secret in your app settings.
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

1. Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthVerifier: Set this to the verifier code.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set this to REFRESH.
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the following properties set:

• OAuthSettingsLocation: Set this to a writable text file.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• CallbackURL: Set this to the Callback URL in your app settings.
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• AccountId: Set this to the specific MID of the target business unit. This is not available for legacy packages.

Create a Custom OAuth App

Create an App

You can follow the procedure below to register an app and obtain the OAuth client credentials, the Client ID and Client Secret:

1. Log in to your Salesforce Marketing Cloud Go to Marketing Cloud | Administration | Installed Packages.
2. Click New.
3. Give the package a name and description.
4. Save the package. Once the package is saved, you see important details about the package. See Installed Packages Definitions for more information about each field. You see the Package ID, JWT Signing Secret, and Source Account only for packages created in your account.
5. Under Components, click Add Component.
6. Select API Integration.
7. You must select Server-to-Server or Web App as integration type if the package supports enhanced functionality.
8. Assign the appropriate scope for your integration.
   • Perform server-to-server requests... is automatically selected for all API Integrations.
   • Select Perform requests on behalf of the user... if this package contains a Marketing Cloud app.
   • Select the Marketing Cloud scope for your API calls. Assign only the scope your package needs.
9. Save the component.
10. Under the component details, you can find the Client ID and Client Secret.

Select From Data Extensions in Salesforce Marketing Cloud

The connector offers the possibility to select, insert, update, and delete data from data extensions as relational tables. To query a data extension, it must be selected in step 1 of a Salesforce Marketing Cloud activity. Data extension names cannot be entered manually.

Select data from data extensions

SELECT * FROM DataExtensionObject_fsefes3

SELECT * FROM DataExtensionObject_fsefes3 WHERE FieldName1 = 'One'

Note 1: All filters which work with normal tables/views also work with data extensions.

Note 2: If your Salesforce Marketing Cloud account contains more than one Business Unit, then the ClientID input has to be specified. You may use semi-colons (;) to specify more than one ClientID.

SELECT * FROM DataExtensionObject_fsefes3 WHERE ClientID = '1234567'

Insert data into data extensions

INSERT INTO DataExtensionObject_fsefes3(FieldName1, FieldName2, FieldName3) VALUES ('One', 'Two', 'Three')

Update data from data extensions

UPDATE DataExtensionObject_secondtest15 SET [Name] = 'ChangedFromTheApi' WHERE [Email Address] = 'update_me@gmail.com'

Note: A primary key has to exist for this data extension for the update to work.

Delete data from data extensions

DELETE FROM DataExtensionObject_secondtest15 WHERE [Email Address] = 'delete_me@gmail.com'

Note: A primary key has to exist for this data extension for the deletion to work.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Salesforce Marketing Cloud connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

User Defined Views

The Salesforce Marketing Cloud connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Subscriber WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The Salesforce Marketing Cloud connector models two Salesforce Marketing Cloud APIs as relational databases, the REST API and the SOAP API. Set the Schema property and use the information in each section to select the proper API based on your project needs.

Collaborative Query Processing

The connector offloads as much of the SELECT statement processing as possible to the Salesforce Marketing Cloud APIs and then processes the rest of the query within the connector.

See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.

Using the REST API

The REST API exposes broader access to Salesforce Marketing Cloud capabilities. All new Salesforce Marketing Cloud technologies implement REST API. See REST Data Model for the available entities in the REST API.

Using the SOAP API

The SOAP API provides comprehensive access to most email functionality. The SOAP API uses SOAP envelopes to pass information between you and Salesforce Marketing Cloud. See SOAP Data Model for the available entities in the SOAP API.

REST Data Model

The connector models the Salesforce Marketing Cloud REST APIs as database Tables and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files that make schemas easy to customize.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables. Tables are statically defined to model Assets, Categories, Journeys, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Salesforce Marketing Cloud. Stored procedures allow you to execute operations to Salesforce Marketing Cloud, including downloading and uploading objects.

Tables

The connector models the data in Salesforce Marketing Cloud into a list of tables that can be queried using standard SQL statements.

Generally, querying Salesforce Marketing Cloud tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Salesforce Marketing Cloud Connector Tables

Assets

Create, update, delete and query assets from your SalesForce Marketing Cloud account.

Table Specific Information

Select

Select all assets:

SELECT * FROM ASSETS

Retrieve a specific asset:

SELECT * FROM ASSETS WHERE ID=20088

Advanced server side filtering using 'AND' and 'OR' logical operators are supported for this table. You can check in the table info if the column has supported filters. All the columns which have supported filters, also can be sorted server side.

SELECT * FROM ASSETS WHERE (Id=5895 OR Id=19442) AND EnterpriseId=7307527 ORDER BY Name ASC, ID DESC

Insert

To create an Asset, you will need to specify at least the Name and TypeId column.

INSERT INTO ASSETS(TypeID,TypeName,Name) VALUES(207,'templatebasedemail','First_Based_Template_Email_')

Update

Assets may be modified by providing the ID of the Asset and issuing an UPDATE statement.

UPDATE ASSETS SET Description='This is an updated asset.', Data='
  "campaigns": {
    "campaigns": [
      {
        "campaignId": 12345,
        "campaignAssociationId": 2387
      }
    ]
  },
"email": { } ... }'  WHERE ID=19442

Delete

Assets may be deleted by providing the ID of the Asset and issuing a DELETE statement.

DELETE FROM ASSETS WHERE ID=20027

Columns

Callbacks

Create, update, delete and query event notification callbacks

Table Specific Information

Select

Select all callbacks:

SELECT * FROM Callbacks

Retrieve a specific callback:

SELECT * FROM Callbacks WHERE CallbackId=94766

Insert

To create a Callback, you will need to specify at least the CallbackName and Url column.

INSERT INTO [Callbacks] (CallbackName, Url) VALUES ('cb1', 'https://example.com')

Update

Callbacks may be modified by providing the CallbackId of the callback and issuing an UPDATE statement.

UPDATE [Callbacks] SET CallbackName = 'cb update' WHERE CallbackId = '34cd6cfe-5a21-4f3e-94c5-b6313a6954a4'

Delete

Callbacks may be deleted by providing the CallbackId of the callback and issuing a DELETE statement.

DELETE FROM [Callbacks] WHERE CallbackId = '43841979-7154-4fc4-9789-909dbba3a54f'

Columns

Campaigns

Create, update, delete and query Campaigns.

Table Specific Information

Select

Select all campaigns:

SELECT * FROM Campaigns

Retrieve a specific Campaign:

SELECT * FROM Campaigns WHERE ID = '3130'

Insert

To create a campaign, you will need to specify at least the Name column.

INSERT INTO [Campaigns] (Name, Description, CampaignCode, Color, Favorite) VALUES ('Test Camp', 'Test Description', 'tst 001', '0000ff', true)

Update

Update operations are not supported for this table.

Delete

Campaigns may be deleted by providing the CampaignId of the campaign and issuing a DELETE statement.

DELETE FROM [Campaigns] WHERE ID = '5161'

Columns

Categories

Create, update, delete and query categories(folders) in Content Builder.

Table Specific Information

Select

Select all categories:

SELECT * FROM Categories

Retrieve all categories which have a specific ParentId:

SELECT * FROM Categories WHERE ParentId=71839

Retrieve a specific category:

SELECT * FROM Categories WHERE Id=94766

All the columns except SharedWith and SharingType can be sorted server side:

SELECT * FROM Categories ORDER BY Name ASC

Insert

To create a Category, you will need to specify at least the Name and ParentId column.

INSERT INTO Categories(Name,ParentId,categoryType) VALUES('New New New Folder',71839,'asset')

Update

Categories may be modified by providing the ID of the category and issuing an UPDATE statement.

Update Categories SET SharedWith='333,555,888', SharingType='edit', EnterpriseId=12345 WHERE Id=71839

Delete

Categories may be deleted by providing the ID of the category and issuing a DELETE statement.

DELETE FROM Categories WHERE Id=94843

Columns

EventDefinitions

Create, delete and query event definitions.

Table Specific Information

Select

Retrieve all event definitions:

SELECT * FROM EventDefinitions

Retrieve a specific event definition:

SELECT * FROM EventDefinitions WHERE Id='9955614b-02e7-4147-91a2-3f5f5fe9d679'

Retrieve all event definitions which are running in a specific mode:

SELECT * FROM EventDefinitions WHERE Mode='Test'

Retrieve all event definitions which contain the specified quoted phrase in their names:

SELECT * FROM EventDefinitions WHERE Contains(Name,'Welcome Journey')

Insert

To create an event definition, you will need to specify at least the Name and Type column. DataExtensionId is also required. If you do not specify it, you must specify the Schema column.

INSERT INTO EventDefinitions(Type,Name,DataExtensionId,IsVisibleInPicker) VALUES('Event','FirstEventDefinition','74bc3342-eaca-e711-b98f-38eaa71427a1',true)

Delete

Event definitions may be deleted by providing the ID of the event definition and issuing a DELETE statement.

DELETE FROM EventDefinitions WHERE Id='f10efb9e-cb91-4fc9-be50-c20f00f7f255'

Columns

FacebookMessengerProperties

Query and create Facebook messenger properties.

Table Specific Information

Select

Retrieve all registred facebook messenger properties:

SELECT * FROM FacebookMessengerProperties

Retrieve a specific registred facebook messenger property:

SELECT * FROM FacebookMessengerProperties WHERE PageId='1732555047025799'

Insert

To register a new facebook messenger property you must specify PageId, ApplicationId, ApplicationSecret, PageName, PageAccessToken, CallbackVerifyToken, EndpointUrl and ApiVersion:

INSERT INTO FacebookMessengerProperties(PageId,ApplicationId,ApplicationSecret,PageName,PageAccessToken,EndpointUrl,CallbackVerifyToken,ApiVersion) VALUES('1732555047025799','1093076390764037','03d537gg656gvkbe9b430f002e9c4517','SFMC Engineers','someaccesstoken4fasdcruib213123knubkdnfisdubnu12312ub3pijnb','https://graph.facebook.com/v2.6/me/messages','this_is_the_verify_token','v2.0')

Columns

JourneyActivities

Create, update and query journey activities.

Table Specific Information

Select

Retrieve journey activities for the latest version of the specified journey:

SELECT * FROM JourneyActivities WHERE JourneyId='1cb643b5-3144-4d17-80fa-a1f0035e78e2'

Retrieve journey activities for a specific journey version:

SELECT * FROM JourneyActivities WHERE JourneyId='1cb643b5-3144-4d17-80fa-a1f0035e78e2' AND JourneyVersion=1

Retrieve journey activities from all journeys with a specific version:

SELECT * FROM JourneyActivities WHERE JourneyVersion=1

Insert

To create a new journey activity, you will need to specify at least the JourneyId, JourneyVersion, Type, Key, Arguments, ConfigurationArguments column.

INSERT INTO JourneyActivities(JourneyId,JourneyVersion,Type,Key,Arguments,ConfigurationArguments) VALUES('4753026f-20b2-481b-89c5-fcd76ffa41f7',1,'WAIT','WAITBYDURATION-1','{
  "waitEndDateAttributeDataBound": "",
  "waitDefinitionId": "f3de0c9a-5ff8-4f7b-84bd-9309ca337227",
  "waitForEventId": "",
  "executionMode": "{{Context.ExecutionMode}}",
  "startActivityKey": "{{Context.StartActivityKey}}",
  "waitQueueId": "{{Context.WaitQueueId}}"
}','{
  "waitDuration": 1,
  "waitUnit": "DAYS",
  "specifiedTime": "",
  "timeZone": "",
  "description": "",
  "waitEndDateAttributeExpression": "",
  "specificDate": "",
  "waitForEventKey": ""
}')

Update

Journey activities may be modified by providing the Id, JourneyId, JourneyVersion and issuing an UPDATE statement.

Update JourneyActivities SET Description='First_Journey_Activity_Description', Outcomes='[
  {
    "key": "11bb2807-3f3d-4305-af51-547df032dbaf",
    "next": "WAITBYDURATION-1",
    "arguments": null,
    "metaData": null
  }
]' WHERE Id='fa4c3d81-8043-40e2-9741-22708d3a2e25' AND  JourneyId='4753026f-20b2-481b-89c5-fcd76ffa41f7' AND JourneyVersion=1

Columns

Journeys

Create, update, delete and query journeys.

Table Specific Information

Select

Retrieve journeys with the most recent version:

SELECT * FROM Journeys

Retrieve a journey version:

SELECT * FROM Journeys WHERE ID='1cb643b5-3144-4d17-80fa-a1f0035e78e2' AND Version=1

Retrieve all journey versions:

SELECT * FROM Journeys WHERE MostRecentVersionOnly=false

Retrieve journeys with a specific tag:

SELECT * FROM Journeys WHERE Tag='First_Tag'

Retrieve journeys which have the specified search string inside the name or description:

SELECT * FROM Journeys WHERE NameOrDescription='Journey'

Retrieve journeys with specific work flow API version:

SELECT * FROM Journeys WHERE workFlowApiVersion=1

Retrieve journeys with specific version:

SELECT * FROM Journeys WHERE Version=1

Retrieve journeys with specific status:

SELECT * FROM Journeys WHERE Status='Draft'

Sort Journeys according to modifiedDate or name column:

SELECT * FROM Journeys Order BY ModifiedDate DESC
SELECT * FROM Journeys Order BY Name DESC

Insert

To create a new journey, you will need to specify at least the Name column.

INSERT INTO Journeys(Name) VALUES('API-Created journey')

To create a new journey version, you will need to specify an existing journey key.

INSERT INTO Journeys(Name,Key) VALUES('API-Created journey Version 3','53bf5ea2-ff59-4c00-a23a-b1e9e333b80c')

Update

Journeys may be modified by providing the Id, Version of the journey and issuing an UPDATE statement.

UPDATE Journeys SET Name='API-Updated journey' WHERE Id='257c51df-d6ed-4fb6-8fbc-70e63ed52b12' AND Version=5

Delete

Journeys may be deleted by providing the ID of the journey and issuing a DELETE statement.

DELETE FROM Journeys WHERE Id='53bf5ea2-ff59-4c00-a23a-b1e9e333b80c'

Journey versions may be deleted by providing the Id, Version of the journey and issuing a DELETE statement.

DELETE FROM Journeys WHERE Id='257c51df-d6ed-4fb6-8fbc-70e63ed52b12' AND Version=5

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

LineMessengerProperties

Query and create Line messenger properties.

Table Specific Information

Select

Retrieve all registred line messenger properties:

SELECT * FROM LineMessengerProperties

Retrieve a specific registred line messenger property:

SELECT * FROM LineMessengerProperties WHERE ID='23493453984234345'

Insert

To register a new line messenger property you must specify ChannelId, ChannelName, ChannelSecret, IsTransactional, IsTestChannel, EndpointUrl and ApiVersion:

INSERT INTO LineMessengerProperties(ChannelId,ChannelName,ChannelSecret,EndpointUrl,IsTransactional,IsTestChannel,IsActive,ApiVersion) VALUES('23493453984234345','SFMC Engineers','03d537gg656gvkbe9b430f002e9c4517','https://example.com/1732555047025799',true,false,true,'v2.0')");

Columns

Subscriptions

Create, update, delete and query event notification subscriptions.

Table Specific Information

Select

Select all subscriptions:

SELECT * FROM Subscriptions

Retrieve a specific subscription:

SELECT * FROM Subscriptions WHERE SubscriptionId=94766

Insert

To create a subscription, you will need to specify at least the SubscriptionName, CallbackId and EventCategoryTypes column.

INSERT INTO [Subscriptions] (SubscriptionName, CallbackId, EventCategoryTypes) VALUES ('sajli subscription', '6fb0758b-155d-4968-869d-7a4f5a3ad2fe', '[\"TransactionalSendEvents.EmailNotSent\", \"TransactionalSendEvents.EmailSent\"]')

Update

Subscriptions may be modified by providing the SubscriptionId and the EventCategoryTypes column of the subscription and issuing an UPDATE statement.

UPDATE [Subscriptions] SET Status = 'paused', EventCategoryTypes = '[\"TransactionalSendEvents.EmailNotSent\", \"TransactionalSendEvents.EmailSent\"]' WHERE SubscriptionId = 'db1e2af0-807d-463b-96e8-fe3aaa019fdb'

Delete

Subscriptions may be deleted by providing the SubscriptionId of the subscription and issuing a DELETE statement.

DELETE FROM [Subscriptions] WHERE SubscriptionId = '43841979-7154-4fc4-9789-909dbba3a54f'

Columns

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Salesforce Marketing Cloud Connector Views

AssetTypes

Get all available asset types in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all asset types:

SELECT * FROM AssetTypes

Columns

CampaignAssets

Retrieve all campaign assets in Salesforce Marketing Cloud.

View Specific Information

Select

Select all campaign assets for a specific campaign:

SELECT * FROM CampaignAssets WHERE CampaignId = '3130'

Retrieve a specific Campaign:

SELECT * FROM CampaignAssets WHERE CampaignId = '3130' AND ID = '3325'

Columns

Contact

Retrieve contacts in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all contacts:

SELECT * FROM Contact

Contacts that are in a deleted, deleting, or restricted state are not retrieved.

Columns

Contacts

Retrieve all contacts in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all contacts:

SELECT * FROM Contacts

Contacts that are in a deleted, deleting, or restricted state are not retrieved.

Columns

JourneyAuditLogs

Retrieves an audit log of a journey and its versions.

Columns

SmsStatusCodes

Query sms status codes.

Table Specific Information

Select

Retrieve all status codes for the following countries: U.S., Canada, Brazil, and India. Use these codes to evaluate and troubleshoot your SMS sends.

SELECT * FROM SmsStatusCodes

Columns

Stored Procedures

Stored procedures are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Salesforce Marketing Cloud Connector Stored Procedures

CreateImportSendDeliveryReport

Generates a .csv fiie containing information regarding SMS message delivery for a specific MessageList and places the report in the Enhanced FTP location for the Marketing Cloud account.

Input

Result Set Columns

CreateKeyword

Creates a keyword on an account.

Input

Result Set Columns

CreateMessageListDeliveryReport

Triggers a delivery report for messageList API.

Input

Result Set Columns

CreateOptInMessage

Creates an SMS opt-in message permitting contacts to subscribe to further SMS messages.

Input

Result Set Columns

DeleteKeyword

Deletes a keyword on an account.

Delete Keyword By Id

Deletes a keyword on an account given a keyword Id.

EXECUTE DeleteKeyword KeywordId='alm5LXNSSktGMGluRznRb1Rb1R5MDZFQTo4Njow'

Delete Keyword By Longcode

Deletes a keyword on an account given a keyword and long code.

EXECUTE DeleteKeyword LongCode='5550003232', Keyword='TEST'

Delete Keyword By Shortcode

Deletes a keyword on an account given a keyword, short code, and country code.

EXECUTE DeleteKeyword ShortCode='89239', Keyword='TEST', CountryCode='US'

Input

Result Set Columns

FireEntryEvent

Fire the entry event that initiates the journey.

Input

Result Set Columns

GetChannelViewHtml

Returns the requested channel view's compiled HTML for the asset.

Input

Result Set Columns

GetDeliveryStatusOfQueuedMO

Retrieves the delivery status of a queued MO.

Input

Result Set Columns

GetFileForAnAsset

Gets the binary file for an asset.

Input

Result Set Columns

GetHeaderFooterAccount

Gets the default header and footer for an account.

Result Set Columns

GetHeaderFooterEmail

Gets the header and footer for an email.

Input

Result Set Columns

GetImportSendStatus

Retrieves the status of a ImportSend automation.

Input

Result Set Columns

GetImportStatus

Retrieves the status of an import job.

Input

Result Set Columns

GetJourneyPublicationStatus

Retrieves the status of a journey publication.

Input

Result Set Columns

GetMessageContactHistory

Retrieves the last message sent to a mobile number.

Input

Result Set Columns

GetMessageContactStatus

Retrieves the overall delivery status of a message to a contact.

Input

Result Set Columns

GetMessageListStatus

Returns status for a message sent to a group of mobile numbers.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from Salesforce Marketing Cloud.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps.

Input

Result Set Columns

GetRefreshListStatus

Retrieves the refresh list status.

Input

Result Set Columns

GetSubscriptionStatus

Returns subscription status for mobile numbers or subscriber keys.

EXECUTE GetSubscriptionStatus MobileNumbers='["15555555555"]'
EXECUTE GetSubscriptionStatus SubscriberKeys='["ExampleSubKey1"]'

Input

Result Set Columns

GetTrackingHistoryOfQueuedMO

Retrieves the tracking history of a queued MO.

Input

Result Set Columns

ImportAndSendMessage

Imports and sends.

EXECUTE ImportAndSendMessage MessageId='MessageId'," +
          "Keyword='Test_Keyword'," +
          "NotificationEmail='myEmail@example.com'," +
          "IsDuplicationAllowed=true," +
          "IsDuplicationAllowed=true," +
          "ImportDefinition='[{" +
          "    \"FileName\": \"MyTestList.csv\"," +
          "    \"ImportType\": \"FILE\"," +
          "    \"ImportMappingType\": \"ManualMap\"," +
          "    \"FieldMaps\": [{" +
          "      \"Destination\": \"_FirstName\"," +
          "      \"Source\": \"First Name\"" +
          "    }, {" +
          "      \"Destination\": \"_Subscriberkey\"," +
          "      \"Source\": \"Subscriber Key\"" +
          "    }, {" +
          "      \"Destination\": \"_LastName\"," +
          "      \"Source\": \"Last Name\"" +
          "    }, {" +
          "      \"Destination\": \"_MobileNumber\"," +
          "      \"Source\": \"Mobile\"" +
          "    }, {" +
          "      \"Destination\": \"_CountryCode\"," +
          "      \"Source\": \"Country\"" +
          "    }]" +
"  }]'

Input

Result Set Columns

PostMessageToList

Initiates a message to one or more contact lists.

EXECUTE PostMessageToList MessageId='NCNSDNsd222as85dj92j2sM',  TargetListIds=' [" +
          "        \"bzZ0cENGam1FZUtNX0poTDRYZzhlQTo2Mzow\"" +
          "    ]', OverrideTemplateTargetLists=true, OverrideTemplateExclusionLists=false, IgnoreExclusionLists=true, OverrideMessageText=false, " +
          "ContentURL='http://image.exct.net/lib/fe6d15707662057c7411/m/1/dj_CC_AUS.jpg'," +
"UtcOffset='-0500', WindowStart='1500', WindowEnd='2200', AllowDuplication=false

Input

Result Set Columns

PostMessageToNumber

Initiates a message to one or more mobile numbers.

Subscribers

The columns available for the Subscribers temporary table are the following:

Execute

Use mobile numbers for referecing contact records:

EXECUTE PostMessageToNumber MessageId='NCNSDNsd222as85dj92j2sM', mobileNumbers='[" +
          "    \"13175551212\"" +
"    ]', Subscribe=true, Resubscribe=true, keyword='JOINSMS', Override=true, messageText='Welcome to Code@', ContentURL='http://image.exct.net/lib/abcd/m/1/dj_CC_AUS.jpg', SendTime='2012-10-05 20:01'

Use Subscribers#Temp table as an alternate way for referecing contact records:

Insert INTO Subscribers#Temp(MobileNumber,SubscriberKey,Attributes) Values('15555554410','ExampleSubKey1','{" +
          "            \"FirstName\":\"Michael\"" +
          "            }')
Insert INTO Subscribers#Temp(MobileNumber,SubscriberKey,Attributes) Values('15555552254','ExampleSubKey2','{" +
          "            \"FirstName\":\"Kristen\"" +
          "            }')
EXECUTE PostMessageToNumber MessageId='NCNSDNsd222as85dj92j2sM', Subscribe=true, Resubscribe=true, Keyword='JOINSMS', Override=false, SendTime='2012-10-05 20:01'

Input

Result Set Columns

PublishJourney

Publishes a journey version asynchronously.

Input

Result Set Columns

QueueContactImport

Queues a contact import.

FieldMaps

The columns available for the FieldMaps temporary table are the following:

Execute

Insert INTO FieldMaps#Temp(destination,ordinal,source) Values('_MobileNumber',2,'mobile number')
Insert INTO FieldMaps#Temp(destination,ordinal,source) Values('_CountryCode',3,'locale')
Insert INTO FieldMaps#Temp(destination,ordinal,source) Values('_SubscriberKey',1,'subscriber key')
EXECUTE QueueContactImport ListId='UEhwdktFWXpFZUs3Z3hRUW45R2dBQTo2Mzow', ShortCode='90913', Keyword='WELCOME', SendEmailNotification=true, EmailAddress='example@example.com'," +
"ImportMappingType='MapByOrdinal', FileName='testdata.csv', FileType='csv', IsFirstRowHeader=true

Input

Result Set Columns

QueueMoMessage

Queues an MO message for send.

EXECUTE QueueMoMessage MobileNumbers='[" +
          "  \"15555551212\"" +
          "  ]', ShortCode='86288', MessageText='CODETEST'
EXECUTE QueueMoMessage Subscribers='[    " +
          "     {   " +
          "       \"mobilenumber\": \"15555551212\",    " +
          "       \"subscriberkey\": \"0_MC1652\"   " +
          "     },    " +
          "     {   " +
          "       \"mobilenumber\": \"15555551213\",    " +
          "       \"subscriberkey\": \"0_MC1652\"   " +
          "     }   " +
"   ]', ShortCode='86288', MessageText='CODETEST'

Input

Result Set Columns

RefreshList

Refreshes a list.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with Salesforce Marketing Cloud.

Input

Result Set Columns

SendMessageToRecipient

Send an OTT message to the recipient. Supported OTT networks are Facebook Messenger and LINE.

MessageContents

You cannot send an OTT message to the recipient without specifying the content of the message. To create message contents, you must insert data in a temporary table called 'MessageContents#TEMP'. The columns available for this temporary table are the following:

MessageCustomKeys

You can also specify message custom keys to pass-through in the message payload by inserting data in a temporary table called 'MessageCustomKeys#TEMP'. The columns available for this temporary table are the following:

Execute

Messenger message send:

Insert INTO MessageContents#Temp(type,text) Values('text','thanks for purchase')
Insert INTO MessageCustomKeys#Temp(messagingType) Values('RESPONSE')
EXECUTE SendMessageToRecipient MessageKey='e1c35141-6e5c-4bc2-813b-60f969e52b0d', MessageGroupKey='CanBeAGUIDorAny100UnicodeCharString', SenderType='messenger', SenderId='503868699681937', OttId='FBfacdb735074f7c492c0bf190fa99020', UserReference='1938cd4d34cc4db0b109756b8a9b14ff', Subject='Message Name', ValidityPeriod=30"

Different Messenger Content Types:

Insert INTO MessageContents#Temp(type,url,AltUrl,IsReusable,AttachmentId) Values('image','https://example.com/original.jpg','https://example.com/preview.jpg',true,12345)
Insert INTO MessageCustomKeys#Temp(messagingType) Values('RESPONSE')
EXECUTE SendMessageToRecipient MessageKey='e1c35141-6e5c-4bc2-813b-60f969e52b0d', MessageGroupKey='CanBeAGUIDorAny100UnicodeCharString', SenderType='messenger', SenderId='503868699681937', OttId='FBfacdb735074f7c492c0bf190fa99020', UserReference='1938cd4d34cc4db0b109756b8a9b14ff', Subject='Message Name', ValidityPeriod=30"

LINE message send:

Insert INTO MessageContents#Temp(type,text) Values('text','thanks for purchase')
Insert INTO MessageCustomKeys#Temp(messagingType) Values('RESPONSE')
EXECUTE SendMessageToRecipient MessageKey='CanBeAGUIDorAny100UnicodeCharString', MessageGroupKey='CanBeAGUIDorAny100UnicodeCharString', SenderType='line', SenderId='2145435435632435', OttId='U42348yafsd8y3248yfsq8cy9088934d', UserReference='1938cd4d34cc4db0b109756b8a9b14ff', Subject='Message Name', ValidityPeriod=30"

Input

Result Set Columns

StopJourney

Stop a running journey.

Input

Result Set Columns

SOAP Data Model

The connector models the Salesforce Marketing Cloud SOAP APIs as database Tables and Views. These are defined in schema files, which are simple, text-based configuration files that make schemas easy to customize.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables. Tables are statically defined to model Assets, Categories, Journeys, and more.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views. Stored procedures allow you to execute operations to Salesforce Marketing Cloud, including downloading and uploading objects.

Tables

The connector models the data in Salesforce Marketing Cloud into a list of tables that can be queried using standard SQL statements.

Generally, querying Salesforce Marketing Cloud tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Salesforce Marketing Cloud Connector Tables

Account

A Marketing Cloud account.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM Account

SELECT * FROM Account WHERE Id = 123

SELECT * FROM Account WHERE Id IN (123, 456)

SELECT * FROM Account WHERE CreatedDate > '2017/01/25'

Insert

You must specify the Name column when executing an insert against this table.

INSERT INTO Account(Name) VALUES('Test')

Update

You must specify the ID in the WHERE clause when executing an update against this table.

UPDATE Account SET Fax='1123123' WHERE Id=123

Delete

You must specify the ID in the WHERE clause when executing a delete against this table.

DELETE FROM Account WHERE ID = 123

Columns

AccountUser

An individual user within an account. This table does not support deletes.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM AccountUser

SELECT * FROM AccountUser WHERE Id = 123

SELECT * FROM AccountUser WHERE Id IN (123, 456)

SELECT * FROM AccountUser WHERE CreatedDate > '2017/01/25'

Insert

You must specify the following fields when inserting to this table: Client_Id, Name, Email, UserID, and Password.

INSERT INTO AccountUser(Client_Id, UserId, Name, Email, Password) VALUES(123, 'bcabsbasbcasb', 'Test', 'test@gmail.com', 'testpas@2sowrd')

Update

You must specify the ID and the Client_Id in the WHERE clause when executing an update against this table.

UPDATE AccountUser SET Name='changed' WHERE Id=123 AND Client_Id=456

Columns

BusinessUnit

A unit within a larger Enterprise or Enterprise 2.0 account. This table supports queries and updates only.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM BusinessUnit

SELECT * FROM BusinessUnit WHERE Id = 123

SELECT * FROM BusinessUnit WHERE Id IN (123, 456)

SELECT * FROM BusinessUnit WHERE CreatedDate > '2017/01/25'

Update

You must specify the ID in the WHERE clause when executing an update against this table.

UPDATE BusinessUnit SET Name='Changed' WHERE Id=123

Columns

ContentArea

A ContentArea represents a defined section of reusable content.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM ContentArea

SELECT * FROM ContentArea WHERE Id = 123

SELECT * FROM ContentArea WHERE Id IN (123, 456)

SELECT * FROM ContentArea WHERE CreatedDate > '2017/01/25'

Insert

You must specify the following fields when inserting to this table: Name and Content.

INSERT INTO ContentArea(Name, Content) VALUES('Testing', 'Hello world')

Update

You must specify the ID in the WHERE clause when executing an update against this table.

UPDATE ContentArea SET Name='Changed' WHERE Id=123

Delete

You must specify the ID in the WHERE clause when executing a delete against this table.

DELETE FROM ContentArea WHERE ID = 123

Columns

DataExtension

Represents a data extension within an account.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM DataExtension

SELECT * FROM DataExtension WHERE CreatedDate > '2017/01/25'

Insert

You must specify the following fields when inserting to this table: Name, CustomerKey, and Fields.

Note: The Salesforce Marketing Cloud APIs have problems with DataExtensions with names longer than 40 characters. Try to limit the name to something relatively short.

INSERT INTO DataExtension (Name, CustomerKey,  Fields) VALUES('TestName', 'TestCustomerKey', 'fieldname1;fieldname2;fieldname3')

Update

You must specify the ObjectId or CustomerKey or Name in the WHERE clause when executing an update against this table.

UPDATE DataExtension SET ResetRetentionPeriodOnImport=true WHERE ObjectId='nzxcaslkjd-123'

Delete

You must specify the ObjectId or CustomerKey or Name in the WHERE clause when executing a delete against this table.

DELETE FROM DataExtension WHERE ObjectId = 'nzxcaslkjd-123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Email

Represents an email in a Marketing Cloud account.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM Email

SELECT * FROM Email WHERE Id = 123

SELECT * FROM Email WHERE Id IN (123, 456)

SELECT * FROM Email WHERE CreatedDate > '2017/01/25'

Insert

You must specify the following fields when inserting to this table: Name and Subject.

INSERT INTO Email(Name, Subject) VALUES('Testing', 'Greetings')

Update

You must specify the ID in the WHERE clause when executing an update against this table.

UPDATE Email SET Name='Changed' WHERE Id=31558

Delete

You must specify the ID in the WHERE clause when executing a delete against this table.

DELETE FROM Email WHERE ID = 123

Columns

EmailSendDefinition

Record that contains the message information, sender profile, delivery profile, and audience information.

Table-Specific Information

Select

The connector uses the Salesforce Marketing Cloud APIs to process the following WHERE clause operators for all but date-time values: =, !=, <>, >, >=, <, <=, IN. For date-time values, only > and < are supported. The connector processes other filters client-side within the connector. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refer to other columns will cause an error.

For example, the following (but not only) queries are processed server side:

SELECT * FROM EmailSendDefinition

SELECT * FROM EmailSendDefinition WHERE ObjectID = 123

SELECT * FROM EmailSendDefinition WHERE ObjectID IN (123, 456)

SELECT * FROM EmailSendDefinition WHERE CreatedDate > '2017/01/25'

Insert

You must specify the following fields when inserting to this table: Name, SendClassification_CustomerKey, and Email_Id.

INSERT INTO EmailSendDefinition(Name, SendClassification_CustomerKey, Email_Id) VALUES('Testing', 13507, 31677)

Update

You must specify the ID in the WHERE clause when executing an update against this table.

UPDATE EmailSendDefinition SET Description='Changed' WHERE ObjectId='acasascas'

Delete

You must specify the ID in the WHERE clause when executing a delete against this table.

DELETE FROM EmailSendDefinition WHERE ObjectId = 'sdfsdf123'

Columns