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 Harmony 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¶