Salesforce Marketing Cloud v2 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8837 of the connector.

Get Started

Salesforce Marketing Cloud Version Support

Establish a Connection

Connect to Salesforce Marketing Cloud

For both REST and SOAP APIs, you have the option to 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.
• Subdomain: If the instance is greater than s10, you must also specify the subdomain.

REST API

To connect, set Schema to REST.

The Salesforce Marketing Cloud REST API uses the OAuth authentication standard. To authenticate using OAuth, you must create a custom OAuth application to obtain values for the OAuthClientId and OAuthClientSecret connection properties. See Creating a Custom OAuth App for more information.

SOAP API

To connect, set Schema to SOAP.

The Salesforce Marketing Cloud SOAP API can connect using OAuth, but also supports a legacy use of login credentials.

Authenticate to Salesforce Marketing Cloud

User Accounts (OAuth)

Set the AuthScheme to OAUTH. Also, in all OAuth flows, set AccountId to the specific MID of the target business unit.

Desktop Apps

Follow the steps below to authenticate with the credentials for a custom OAuth application. See Creating a Custom OAuth App for information about custom OAuth applications. Get an OAuth Access Token

After setting the following, you are ready to connect:

• OAuthClientId: The Client ID in your application settings.
• OAuthClientSecret: The Client Secret in your application settings.
• CallbackURL: The Redirect URL in your application settings.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

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. These values persist across connections.

To connect to data, set the OAuthAccessToken connection property to the access token returned by the stored procedure. When the access token expires after ExpiresIn seconds, call GetOAuthAccessToken again to obtain a new access token.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an Internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an Internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:

   • If you are using the Embedded OAuth Application click Salesforce Marketing Cloud OAuth endpoint to open the endpoint in your browser.

   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:

     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client ID assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.

     Then call the rpgrestsp-getoauthauthorizationurl stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser. 2. 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. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

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

• InitiateOAuth: REFRESH.
• OAuthVerifier: The verifier code.
• OAuthClientId: (custom applications only) The Client ID in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) The Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: The location of the settings file where OAuth values are saved when you set InitiateOAuth to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'. When this connection property is set, the data persists across connections.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: REFRESH.
• OAuthClientId: (custom applications only) The client ID assigned when you registered your application.
• OAuthClientSecret: (custom applications only) The client secret assigned when you registered your application.
• OAuthSettingsLocation: The location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an Internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

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:

• OAuthClientId: (custom applications only) The client ID assigned when you registered your application.
• OAuthClientSecret: (custom applications only) The client secret assigned when you registered your application.
• OAuthSettingsLocation: The location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Server-to-Server (OAuthClient)

When creating an application in Salesforce Marketing Cloud, you can select server-to-server authentication. In this case, the application's permissions are configured directly in the Salesforce Marketing Cloud UI. As such, there is no user context and hence no browser-based login or permission grants. For this scheme, you must create your own credentials.

Specify the following properties to enable server-to-server OAuth authentication for your application:

• AuthScheme: OAuthClient.
• InitiateOAuth: GETANDREFRESH.
• OAuthClientId: The client Id.
• OAuthClientSecret: The client secret.
• Subdomain: Specify the Salesforce Marketing Cloud API subdomain.

User/Password Accounts (Basic)

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

Note that this authentication scheme is not available for REST API-based applications.

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

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

Create a Custom OAuth App

Create an App

Follow the procedure below to create and register an application and obtain the OAuth client credentials, the client Id, and client secret:

1. Log in to your Salesforce Marketing Cloud and navigate to Marketing Cloud | Administration | Installed Packages.
2. Click New.
3. Specify a package name and description.
4. Save the package. The saved package contains important data; see "Installed Packages Definitions" for more information about each field. Note that 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 the 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 application.
   • Select the Marketing Cloud scope for your API calls. Assign only the scope your package needs.
9. Save the component.
10. The Client ID and Client Secret are located under the component details.

Select From Data Extensions in SalesforceMarketingCloud

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 v2 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: By default the Salesforce Marketing Cloud retrieves data for the LoggedIn ClientId. To retrieve results for more than one ClientID, use semi-colons (;) as a separator. The ClientID can be accounts and sub-accounts, including Enterprise 2.0, On-Your-Behalf, and Lock & Publish accounts.

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.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Salesforce Marketing Cloud and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Jitterbit Connector for Salesforce Marketing Cloud 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"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

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 Jitterbit Connector for Salesforce Marketing Cloud 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.

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 as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Salesforce Marketing Cloud 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

SendDefinitions

Create, update, delete and query send definitions.

Table Specific Information

Select

Retrieve all send definitions:

SELECT * FROM SendDefinitions

Retrieve a specific send definition:

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

Retrieve all send definitions with Status as 'Active':

SELECT * FROM SendDefinitions WHERE Status = 'Active'

Insert

To create a send definition, you will need to specify at least the DefinitionKey, Name, SubscriptionsList and ContentCustomerKey column.

INSERT INTO SendDefinitions (DefinitionKey, Name, ContentCustomerKey, SubscriptionsList, OptionsCc, OptionsCreateJourney) VALUES ('TEST_Definition_Key', 'Test Definition Key', '76ad3572-abbc-4baa-b3fe-04c4364bf34a', 'All Subscribers', 'john@example.com', true)

Update

To update a send definition, you will need to specify the DefinitionKey of the SendDefinition.

UPDATE SendDefinitions SET OptionsCc = 'john@example.com,steve@example.com', OptionsBcc = 'michael@example.com,richard@example.com' WHERE DefinitionKey = 'TEST_Definition_Key'

Delete

Send definitions may be deleted by providing the Definition Key of the send definition and issuing a DELETE statement.

DELETE FROM SendDefinitions WHERE DefinitionKey = 'TEST_Definition_Key'

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 similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Jitterbit Connector for Salesforce Marketing Cloud 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

JourneyHistory

Gives the history of a journey. Record access cannot exceed 10,000th item

Table Specific Information

Select

Retrieves information about a specific contact, journey, or journey version.:

SELECT * FROM JourneyHistory

Retrieves information about a specific contact, journey, or journey version within the specified date range.:

SELECT * FROM JourneyHistory where TransactionTime >= '2022-09-01T10:29:22.438Z' and TransactionTime <= '2022-09-30T11:29:22.438Z'

SELECT * FROM JourneyHistory where TransactionTime > '2022-09-01T10:29:22.438Z' and TransactionTime < '2022-09-30T11:29:22.438Z'

SELECT * FROM JourneyHistory where TransactionTime = '2022-09-01T10:29:22.438Z'

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

TransactionalMessages

Gets a paginated list of messages that were not sent, ordered from oldest to newest.

Table Specific Information

Select

Select all TransactionalMessages:

SELECT * FROM TransactionalMessages

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Salesforce Marketing Cloud.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Salesforce Marketing Cloud, along with an indication of whether the procedure succeeded or failed.

Jitterbit Connector for Salesforce Marketing Cloud 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

CreateTriggeredSend

Create a triggered send object which represents a specific instance of a triggered email send.

Table Specific Information

Subscriber Attributes

To create SubscriberAttributes, you must insert data in a temporary table called 'Subscribers#TEMP'.

INSERT INTO Subscribers#Temp(Order_Number,Order_Status,Purchase_Date) VALUES (1234,'received','2015-06-30 11:10:36.956')

EXECUTE CreateTriggeredSend  key='TEST_1', FromAddress='test123@salesforce.com', FromName='test', ToAddress='arctest42@gmail.com',SubscriberKey='12345678', Subscribers='Subscribers#Temp'

Execute

you can execute the stored procedure.

EXECUTE CreateTriggeredSend  key='TEST_1', FromAddress='test123@salesforce.com', FromName='test', ToAddress='arctest42@gmail.com',SubscriberKey='12345678'

EXECUTE CreateTriggeredSend  key='TEST_1', FromAddress='test123@salesforce.com', FromName='test', ToAddress='arctest42@gmail.com', SubscriberKey='12345678', SubscriberAttributes='{\"attrname\":\"test\",\"attrname2\":22,\"attrname3\":\"testing\"}'

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

GetMessageSendStatus

Gets the send status for a message.

EXECUTE GetMessageSendStatus MessageKey = 'bcX0qaEp0USGciEnUJTW0w'

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from SalesforceMarketingCloud.

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 SalesforceMarketingCloud.

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

SendTransactionalMessageToMultipleRecipients

Sends a message to a single recipient via a send definition.

Execute

Transactional message send:

For RecipientAggregate and Attributes either JSON or temp table as input. For example,

INSERT INTO RecipientAggregate#TEMP (RecipientContactKey, RecipientTo, RecipientMessageKey, RecipientAttributes) VALUES ('recipient1', 'recipient1@example.com', 'nFL4ULgheUeaGbPIMzJJSw', '{"RequestAttribute_1":"value_1", "RequestAttribute_2":"value_2", "Attribute1":"This is one for recipient1", "Attribute2":"This is two for recipient1"}');
INSERT INTO RecipientAggregate#TEMP (RecipientContactKey, RecipientTo, RecipientMessageKey, RecipientAttributes) VALUES ('recipient2', 'recipient2@example.com', 'GV1LhQ6NFkqFUAE1IsoQ9Q', '{"UserAttribute_3":"value_3", "UserAttribute_4":"value_4"}');

EXECUTE SendTransactionalMessageToMultipleRecipients DefinitionKey = '2FA_order_accounts', RecipientAggregate = 'RecipientAggregate#TEMP', Attributes = '{"UserAttribute_a":"value_a", "UserAttribute_b":"value_b"}'

INSERT INTO Attributes#TEMP (UserAttr_1, UserAttr_2) VALUES ('UserAttrValue_1', 'UserAttrValue_2');

INSERT INTO RecipientAggregate#TEMP (RecipientContactKey, RecipientTo, RecipientMessageKey, RecipientAttributes) VALUES ('recipient1', 'recipient1@example.com', 'nFL4ULgheUeaGbPIMzJJSw', '{"RequestAttribute_1":"value_1", "RequestAttribute_2":"value_2", "Attribute1":"This is one for recipient1", "Attribute2":"This is two for recipient1"}');
INSERT INTO RecipientAggregate#TEMP (RecipientContactKey, RecipientTo, RecipientMessageKey, RecipientAttributes) VALUES ('recipient2', 'recipient2@example.com', 'GV1LhQ6NFkqFUAE1IsoQ9Q', '{"UserAttribute_3":"value_3", "UserAttribute_4":"value_4"}');


EXECUTE SendTransactionalMessageToMultipleRecipients DefinitionKey = '2FA_order_accounts', RecipientAggregate = 'RecipientAggregate#TEMP', Attributes = 'Attributes#TEMP'

Input

Result Set Columns

SendTransactionalMessageToRecipient

Sends a message to a single recipient via a send definition.

Execute

Send a transactional message:

Attributes support either JSON or temp table as input. For example,

EXECUTE SendTransactionalMessageToRecipient MessageKey = 'e1c35141-6e5c-4bc2-813b-60f969e52b0d', DefinitionKey = 'CanBeAGUIDorAny100UnicodeCharString', RecipientContactKey = 'd3c4a2d2-b620-4a39-88aa-b14868b766c6', RecipientTo = 'john@example.com', Attributes = '{"UserAttr_1":"UserAttrValue_1","UserAttr_2":"UserAttrValue_2"}'

INSERT INTO Attributes#TEMP (UserAttr_1, UserAttr_2) VALUES ('UserAttrValue_1', 'UserAttrValue_2');

EXECUTE SendTransactionalMessageToRecipient MessageKey = 'e1c35141-6e5c-4bc2-813b-60f969e52b0d', DefinitionKey = 'CanBeAGUIDorAny100UnicodeCharString', RecipientContactKey = 'd3c4a2d2-b620-4a39-88aa-b14868b766c6', RecipientTo = 'john@example.com', Attributes = 'Attributes#TEMP';

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 as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Salesforce Marketing Cloud 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.

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

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.

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

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.

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

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