This page provides a guide to Establishing a Connection to Salesforce Marketing Cloud in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Salesforce Marketing Cloud

Establishing a Connection shows how to authenticate to Salesforce Marketing Cloud and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Salesforce Marketing Cloud through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Salesforce Marketing Cloud by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

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

Note: Data extension objects in Salesforce Marketing Cloud are only accessible through the SOAP API.

Authenticating 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. NOTE: This is not available for legacy packages. The following OAuth sections assume that you have set both these connection properties.

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.

When you connect the Cloud opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. 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 Cloud 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 Cloud 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 Cloud. 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 Cloud 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 Cloud 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 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.

The Cloud offers the possibility to select, insert, update, and delete data from data extensions as relational tables. To query a data extension, simple enter its name in the format DataExtensionObject_Name where Name is the name of your data extension.

Note: To connect to data extension objects in Salesforce Marketing Cloud, you must use the SOAP API. Set Schema to SOAP.

Selecting 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'

Inserting data into data extensions

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

Updating data from data extensions

UPDATE DataExtensionObject_secondtest15 SET [Name] = 'ChangedFromTheApi' WHERE [Email Address] = '[email protected]'

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

Deleting data from data extensions

DELETE FROM DataExtensionObject_secondtest15 WHERE [Email Address] = '[email protected]'

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

Customizing the SSL Configuration

By default, the Cloud 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.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData 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.

The Cloud 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.

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

CData Cloud - Salesforce Marketing Cloud Tables

Name	Description
Assets	Create, update, delete and query assets from your SalesForce Marketing Cloud account.
Callbacks	Create, update, delete and query event notification callbacks
Campaigns	Create, update, delete and query Campaigns.
Categories	Create, update, delete and query categories(folders) in Content Builder.
EventDefinitions	Create, delete and query event definitions.
FacebookMessengerProperties	Query and create Facebook messenger properties.
JourneyActivities	Create, update and query journey activities.
Journeys	Create, update, delete and query journeys.
LineMessengerProperties	Query and create Line messenger properties.
SendDefinitions	Create, update, delete and query send definitions.
Subscriptions	Create, update, delete and query event notification subscriptions.

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_CData')

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=,!=,<,<=,>,>=	The Id of the Asset.
CustomerKey	String	False		=,!=	Reference to customer's private ID/name for the asset.
ObjectId	String	True			System-controlled, read-only text string identifier for object.
Name	String	False		=,!=	Name of the asset, set by the client.
Description	String	False		=,!=	Description of the asset, set by the client.
OwnerId	Integer	True		=,!=,<,<=,>,>=	The Id of the asset owner.
OwnerName	String	True		=,!=	The name of the asset owner.
OwnerEmail	String	True		=,!=	The email of the asset owner.
OwnerUserId	Integer	True			The user Id of the asset owner.
CreatedDate	Datetime	True		=,!=,<,<=,>,>=	The datetime when the asset was created.
CreatorId	Integer	True		=,!=,<,<=,>,>=	The Id of the asset creator.
CreatorName	String	True			The name of the asset creator.
CreatorEmail	String	True			The email of the asset creator.
CreatorUserId	Integer	True			The user Id of the asset creator.
ModifiedDate	Datetime	True		=,!=,<,<=,>,>=	The last datetime when the asset was modified.
ModifierId	Integer	True		=,!=,<,<=,>,>=	The Id of the asset modifier.
ModifierName	String	True			The name of the asset modifier.
ModifierEmail	String	True			The email of the asset modifier.
ModifierUserId	Integer	True			The user Id of the asset modifier.
EnterpriseId	Integer	True		=,!=,<,<=,>,>=	A short description of the ...
MemberId	Integer	True		=,!=,<,<=,>,>=	Marketing Cloud MID (member ID) for your account.
ActiveDate	Datetime	False		=,!=,<,<=,>,>=	A short description of the ...
ExpirationDate	Datetime	False		=,!=,<,<=,>,>=	A short description of the ...
ContentType	String	False		=,!=	The type that the content attribute will be in.
TypeId	Integer	False		=,!=,<,<=,>,>=	The Id of the asset type.
TypeName	String	False			The name of the asset type.
TypeDisplayName	String	False			The display name of the asset type.
CategoryId	Integer	False		=,!=,<,<=,>,>=	The Id of the category where the asset is located.
CategoryName	String	False			The name of the category where the asset is located.
CategoryParentId	Integer	False			The Parent Id of the category where the asset is located.
Content	String	False		=,!=	The actual content of the asset.
Design	String	False		=,!=	Fallback for display when neither content nor supercontent are provided.
SuperContent	String	False		=,!=	Content that supersedes content in terms of display.
File	String	False			Base64-encoded string of a file associated with an asset.
FileProperties	String	False			Stores the different properties that this asset refers to if it is a file type.
ForwardHtml	String	False			Field inside the views object.
ForwardText	String	False			Field inside the views object.
HtmlContent	String	False			Field inside the views object.
HtmlSlots	String	False			Field inside the views object.
HtmlTemplate	String	False			Field inside the views object.
Preheader	String	False			Field inside the views object.
SubjectLine	String	False			Field inside the views object.
SubscriptionCenter	String	False			Field inside the views object.
Text	String	False			Field inside the views object.
ViewAsAWebPage	String	False			Field inside the views object.
GenerateFrom	String	False			Tells the sending compiler what view to use for generating this view's content.
Slots	String	False			Slots within the asset.
Blocks	String	False			Blocks within the asset.
MinBlocks	Integer	False			Minimum number of blocks within an asset.
MaxBlocks	Integer	False			Maximum number of blocks within an asset.
AllowedBlocks	String	False			List of blocks that are allowed in the asset.
Template	String	False			Template the asset follows.
CustomFields	String	False			Custom fields within an asset.
Data	String	False			Property bag containing the asset data.
Channels	String	False			List of channels that are allowed to use this asset.
Version	Integer	True			The version of the asset.
Locked	Boolean	False			Specifies if the asset can be modified or not.
Status	String	False			The asset's status.
Tags	String	False			List of tags associated with the asset.
BusinessUnitAvailability	String	False			A dictionary of member IDs that have been granted access to the asset.
SharingProperties	String	False			Allows you to share content with one or more business units that have Content Builder Sharing enabled.

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

Name	Type	ReadOnly	References	Filters	Description
CallbackId [KEY]	String	False		=	The Id of the event notification callback.
CallbackName	String	False			The name of the event notification callback.
Url	String	False			The url of the event notification callback.
MaxBatchSize	Integer	False			Maximum batch size of the event notification callback.
Status	String	False			The status of the event notification callback.
StatusReason	String	False			The status reason of the event notification callback.

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=	The Id of the Campaign.
Name	String	False			Name of the Campaign.
CampaignCode	String	False			The Campaign code.
Color	String	False			The Campaign color.
Favorite	Boolean	False			True if marked as favorite Campaign.
CreatedDate	Datetime	False			The exact time when the Campaign was created.
ModifiedDate	Datetime	False			Last time the Campaign was updated.
Description	String	False			Description of the Campaign.

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	Integer	True		=	The Id of the category(folder) in Content Builder.
Name	String	False			Name of the category.
ParentId	Integer	False		=	ID of the parent category.
CategoryType	String	False			The type of category, either asset or asset-shared, which is automatically set to the CategoryType of the parent category. If set to asset-shared, include the SharingProperties in the call.
EnterpriseId	Integer	False			ID of the enterprise this business unit belongs to.
MemberId	Integer	False			ID of the member who creates the category.
SharedWith	String	False			List of up to 100 MID IDs the category is shared with. To share the category with all business units in the enterprise, and if your account has access to Content Builder Across Enterprise Sharing, set this to 0. SharedWith cannot contain 0 and other MIDs simultaneously. Since shared categories live in and are owned by the enterprise business unit, don't include the enterprise business unit in the SharedWith property.
SharingType	String	False			Indicates the permission that you are granting to the list of MIDs in sharedWith. The only possible value for categories is edit. The allowed values are edit.
Description	String	False			Description of the category.

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True		=	The Id of the event definition.
Type	String	False			The type of the event definition. The allowed values are Event, ContactEvent, DateEvent, RestEvent.
Name	String	False		Contains	A name for your Event Definition that identifies the event in Marketing Cloud.
CreatedDate	Datetime	True			The datetime when the event definition was created.
CreatedBy	Integer	True			The Id of the event definition creator.
ModifiedDate	Datetime	True			The datetime when the event definition was updated.
ModifiedBy	Integer	True			The Id of the event definition modifier.
Mode	String	False		=	The mode in which the event definitions can run. The allowed values are Production, Test. The default value is Production.
EventDefinitionKey	String	False			Unique Id for an Event Definition in Marketing Cloud. Don't include special characters.
DataExtensionId	String	False			The ID for the data extension associated with the event. Events fired from the API write to this data extension. Required only when not providing a schema.
DataExtensionName	String	False			The read-only name of the data extension associated with the event.
Description	String	False			The description of Event Definition.
Schema	String	False			Schema information for an event. The call uses this information to create a data extension associated with the Event Definition. Only required when not providing a dataExtensionId value.
SendableCustomObjectField	String	False			Defines the subscriber key or email address within a data extension. Required when defining a schema.
SendableSubscriberField	String	False			Indicates type of sendable subscriber field. The default value is SubscriberKey. Required when defining a schema.
Schedule	String	False			Creates an automation run daily according to the defined schedule. The call automatically adds a Fire Event activity to the automation. This automation fires events from the data extension defined as part of the Event Definition.
FilterDefinitionId	String	False			The Id of the filter definition.
FilterDefinitionTemplate	String	False			The template of the filter definition.
IconUrl	String	False			URL to an icon displayed in Event Administration and the Journey Builder Canvas that identifies an Event Definition.
Arguments	String	False			Contains filter criteria for the Event Definition. Only required for types other than event.
Configuration	String	False			Optional configuration data for the event. Only required for types other than event.
ConfigurationArguments	String	False			The configuration arguments of Event Definition.
Metadata	String	False			Optional data describing the event and its configuration. Only required for types other than event.
InteractionCount	Integer	False			The interaction counter.
IsVisibleInPicker	Boolean	False			Indicates whether the Event Picker shows the Event Definition to users when configuring a journey. The default value is true.
Category	String	False			Broad category of the event type. The default value is event.
PublishedInteractionCount	Integer	False			The published interaction counter.
AutomationId	String	False			The Id of the Automation.

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

Name	Type	ReadOnly	References	Filters	Description
PageId [KEY]	String	False		=	The PageId of the Facebook Messenger property.
ApplicationId	String	False			Facebook Application Id.
ApplicationSecret	String	False			Facebook Application Secret.
PageName	String	False			Facebook Page Name.
PageAccessToken	String	False			Access Token required to authenticate send messages requests to Facebook networks.
CallbackVerifyToken	String	False			Facebook Verify Token need to verify callback events.
EndpointUrl	String	False			Send API url of the Facebook network.
IsActive	Boolean	False			Indicate if the Facebook Messenger resource is active.
ApiVersion	String	False			The API version of the OTT property.

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True			This is the Marketing Cloud-provided unique ID for this particular activity.
JourneyId [KEY]	String	True	Journeys.Id	=	A unique identifier for this journey that is generated and assigned by the journey API during creation.
JourneyVersion [KEY]	Integer	True	Journeys.Version	=	This number denotes the iteration of this particular journey.
Key	String	False			This is the customer key (journey-unique) for this particular activity.
Name	String	False			This is the display name for this activity, it will be shown in the Journey Builder UI.
Description	String	False			This is the description for this activity.
Type	String	False			This property defines what type of activity this is. The expected input for each activity must be passed as an argument to operate correctly.
Outcomes	String	False			This is a json array of 'outcome' objects.
Arguments	String	False			This is an object which represents the arguments this particular activity expects to be passed for use at runtime. Each activity type has its own unique argument parameters which are expected.
ConfigurationArguments	String	False			This is an object which represents the arguments this particular activity expects to be passed for use both at publish and runtime. Each activity type has its own unique argument parameters which are expected.

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

Name	Type	ReadOnly	References	Filters	Description
Id [KEY]	String	True		=	A unique identifier for this journey that is generated and assigned by the journey API during creation.
Version	Integer	True		=	This number denotes the iteration of this particular journey.
Key	String	False			The customer key (also a unique identifier within the MID) for this journey.
Name	String	False			The display name used in the Journey Builder UI for this journey, this will be visible to everyone who logs into your Marketing Cloud account.
Status	String	False		=	A status description upon which to filter journeys. The allowed values are Draft, Published, ScheduledToPublish, Stopped, Unpublished, Deleted.
CreatedDate	Datetime	True			The datetime when the journey was created.
ModifiedDate	Datetime	True			The datetime when the journey was updated.
LastPublishedDate	Datetime	True			The datetime when the journey was last published.
Description	String	False			The human readable description of this journey that informs others of purpose.
WorkFlowApiVersion	Integer	False		=	The version of work flow API. The default value is 1.
Tags	String	False			Assigned tags to the journey.
Goals	String	False			This is an array of goals containing a single object.
Triggers	String	False			This is an array of triggers containing a single object.
Defaults	String	False			This object contains an ordered list of email expressions used to determine which email address to use as the default, starting with the first expression.

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.

Name	Type	Description
MostRecentVersionOnly	Boolean	A flag to indicate whether to fetch only the most recent version of matching journeys. The default value is true.
Tag	String	Specify a single tag to filter results to only include journeys associated with that tag.
NameOrDescription	String	A search string inside the journey's name or description properties upon which to match for filtering.

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

Name	Type	ReadOnly	References	Filters	Description
ChannelId [KEY]	String	False		=	The Id of the LINE Messenger property.
ChannelName	String	False			LINE channel name.
ChannelSecret	String	False			LINE channel secret.
CustomerConnectSecret	String	False			LINE Customer Connect Secret needed for validating LINE inbound events when using Switcher API.
IsTransactional	Boolean	False			Indicate if LINE channel is Transactional or Reseller.
IsTestChannel	Boolean	False			Indicate if LINE channel is a Test channel.
EndpointUrl	String	False			Send API url of the LINE network.
IsActive	Boolean	False			Indicate if the LINE Messenger resource is active.
ApiVersion	String	False			The API version of the OTT property.

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', '[email protected]', true)

Update

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

UPDATE SendDefinitions SET OptionsCc = '[email protected],[email protected]', OptionsBcc = '[email protected],[email protected]' 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

Name	Type	ReadOnly	References	Filters	Description
DefinitionKey [KEY]	String	False		=	Unique, user-generated key to access the definition object.
DefinitionId	String	True			Unique Marketing Cloud object identifier.
Classification	String	False			Marketing Cloud external key of a sending classification defined in Email Studio Administration. Only transactional classifications are permitted. Default is default transactional.
ContentCustomerKey	String	False			Unique identifier of the content asset.
CreatedDate	Datetime	True			Date the definition was created.
Description	String	False			User-provided description of the send definition.
Journey	Boolean	True			Indicates whether the send definition is available in Journey Builder as a Transactional Send Journey.
JourneyInteractionKey	String	True			Unique ID of the Transactional Send Journey.
ModifiedDate	Datetime	True			Date and time the definition was most recently changed.
Name	String	False			Name of the definition.
OptionsBcc	String	False			Include BCC email addresses with every send. To dynamically BCC at send time, create a profile attribute and use the %%attribute%% syntax.
OptionsCc	String	False			Include CC email addresses with every send. To CC dynamically at send time, create a profile attribute and use the %%attribute%% syntax.
OptionsTrackLinks	Boolean	False			Wraps links for tracking and reporting. Default is true.
OptionsCreateJourney	Boolean	False			Defines the subscriber key or email address within a data extension. Required when defining a schema.
RequestId	String	True			The unique identifier of this request.
Status	String	False		=,!=	Operational state of the definition: active, inactive, or deleted. A message sent to an active definition is processed and delivered. A message sent to an inactive definition isn't processed or delivered. Instead, the message is queued for later processing for up to three days.
SubscriptionsAutoAddSubscriber	Boolean	False			Adds the recipient's email address and contact key as a subscriber key to subscriptions.list. Default is true.
SubscriptionsDataExtension	String	False			Marketing Cloud external key of the triggered send data extension. Each request inserts as a new row in the data extension.
SubscriptionsList	String	False			Marketing Cloud external key of the list or all subscribers. Contains the subscriber keys and profile attributes.
SubscriptionsUpdateSubscriber	Boolean	False			For email only: Updates the recipient's contact key as a subscriber key with the provided email address and profile attributes to subscriptions.list. Default is true.

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

Name	Type	ReadOnly	References	Filters	Description
SubscriptionId [KEY]	String	False		=	The Id of the event notification subscription.
SubscriptionName	String	False			The name of the event notification subscription.
CallbackId	String	False	Callbacks.CallbackId	=	The Id of the event notification callback.
CallbackName	String	False			The name of the event notification callback.
EventCategoryTypes	String	False			Event category types.
Filters	String	False			Filters.
Url	String	False			The url of the event notification callback.
MaxBatchSize	Integer	False			Maximum batch size of the event notification callback.
Status	String	False			The status of the event notification callback.
StatusReason	String	False			The status reason of the event notification callback.

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.

CData Cloud - Salesforce Marketing Cloud Views

Name	Description
AssetTypes	Get all available asset types in Salesforce Marketing Cloud.
CampaignAssets	Retrieve all campaign assets in Salesforce Marketing Cloud.
Contact	Retrieve contacts in Salesforce Marketing Cloud.
Contacts	Retrieve all contacts in Salesforce Marketing Cloud.
JourneyAuditLogs	Retrieves an audit log of a journey and its versions.
JourneyHistory	Gives the history of a journey. Record access cannot exceed 10,000th item
SmsStatusCodes	Query sms status codes.
TransactionalMessages	Gets a paginated list of messages that were not sent, ordered from oldest to newest.

Get all available asset types in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all asset types:

SELECT * FROM AssetTypes

Columns

Name	Type	References	Description
Id [KEY]	Integer	Assets.TypeId	The Id of the asset type.
Name	String		The name of the asset type.
IsBaseAssetType	Boolean		Whether the asset type is a base asset type or not.
ParentId	Integer		The Id of the parent asset type.

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

Name	Type	References	Description
Id [KEY]	Integer		The Id of the campaign asset.
CampaignId	Integer	Campaigns.Id	CampaignId assosiated with the asset.
Type	String		Type of the campaign asset.
ItemId	String		Internal object id.
CreatedDate	Datetime		The exact time when the asset was created.

Retrieve contacts in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all contacts:

SELECT * FROM Contact

Note: Most columns for this table are dynamic so they may not be the same as the columns specified below because you can have a different Contact schema in your Salesforce marketing cloud account.
Contacts that are in a deleted, deleting, or restricted state are not retrieved.

Columns

Name	Type	References	Description
ContactID [KEY]	Int		The system defined identifier of the contact.
ContactKey	String		The user defined identifier of the contact.

Retrieve all contacts in Salesforce Marketing Cloud.

Table Specific Information

Select

Retrieve all contacts:

SELECT * FROM Contacts

Note: Most columns for this table are dynamic so they may not be the same as the columns specified below because you can have a different Contact schema in your Salesforce marketing cloud account.
Contacts that are in a deleted, deleting, or restricted state are not retrieved.

Columns

Name	Type	References	Description
GroupConnect LINE Demographics.Address ID	String		This is an autogenerated description
GroupConnect LINE Demographics.Contact ID	Long		This is an autogenerated description
GroupConnect LINE Demographics.Contact Key	String		This is an autogenerated description
Contact.Contact ID [KEY]	Int		The system defined identifier of the contact.
Contact.Contact Key	String		The user defined identifier of the contact.
Email Demographics.Contacts ID	Long		This is an autogenerated description
Email Demographics.NewAttrTest	String		Test new attr
Email Demographics.NewAttrTest1	String		New Attr test
Email Demographics.NewAttrTest 2	String		NewAttrTest 2
Email Demographics.tet	String		test
Email Addresses.Email Address	String		This is an autogenerated description
Email Addresses.HTML Enabled	Bool		This is an autogenerated description
MobileConnect Demographics.Contact ID	Long		This is an autogenerated description
MobileConnect Demographics.Locale	String		This is an autogenerated description
MobileConnect Demographics.Mobile Number	String		This is an autogenerated description
MobilePush Demographics.Application	String		This is an autogenerated description
MobilePush Demographics.Contact ID	Long		This is an autogenerated description
MobilePush Demographics.Device ID	String		This is an autogenerated description
Contact.Business Unit ID	Int		This is an autogenerated description
Email Addresses.Member ID	Int		This is an autogenerated description
Email Addresses.List ID	Int		This is an autogenerated description

Retrieves an audit log of a journey and its versions.

Columns

Name	Type	References	Description
JourneyId	String	Journeys.Id	A unique identifier for this journey that is generated and assigned by the journey API during creation.
JourneyVersion	Integer	Journeys.Version	This number denotes the iteration of this particular journey.
Key	String		The customer key (also a unique identifier within the MID) for this journey.
Action	String		The actions used to build your audit log. The allowed values are all, create, modify, publish, unpublish, delete. The default value is all.
Name	String		This is the display name for this activity, it will be shown in the Journey Builder UI.
Description	String		This is the description for the journey.
ActionDate	Datetime		The datetime of the action.
UserId	Integer		Id of user who performed the action.
UserName	String		Name of user who performed the action.
ExecutionMode	String		The execution mode in which the journey is running.
OriginalDefinitionId	String		The original definition Id of the journey.
PublishRequestId	String		The Id of the publish request.
PublishStatus	String		The status of the journey publication.
Errors	String		Errors occurred during the action.
ContactsEjected	String		Ejected Contacts when journey is stopped.

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'

NOTE: Max 10k records can be retrieved using JourneyHistory. If only greater_equals or greater TransactionTime is mentioned it will retrieve maximum 10k records starting from that TransactionTime. If both greater_equals or greater and smaller_equals or smaller is mentioned, it will fetch the first 10k records.

Columns

Name	Type	References	Description
Id	String		A unique identifier for this journeyhistory that is generated and assigned by the journey API when the journey is in running state.
ActivityBatchInstanceId	String		ActivityBatchInstanceId
ActivityId	String		A unique identifier for this activity that is generated and assigned by the journey API when the activity is created.
ActivityType	String		Type of Activity
ClientStatus	String		Client Status
ContactKey	String		The customer key (also a unique identifier within the MID) for this journey.
CreatedDate	Datetime		The datetime when the journey was created.
DefinitionId	String		Definition Id
DefinitionInstanceId	String		Definition Instance Id
DefinitionName	String		Definition Name
EpochTimeInMilliseconds	Long		Epoch Time In Milli Seconds
EventId	String		Event Id
EventName	String		Event Name
LongId	Integer		Id
Message	String		Message
Mid	Integer		MID
ResultMessages	String		Messages
ResultStatus	String		Status
ResultTags	String		Tags
Status	String		Status of Journey History
TransactionTime	Datetime		Transaction Time

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

Name	Type	References	Description
Code [KEY]	Integer		The SMS code.
Status	String		The SMS status for the respective Code.
Definition	String		The definition for the SMS status Code.

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

Name	Type	References	Description
LastEventId	Integer		The ID of the Event from which you want the response to start.
StatusCode	Integer		Integer value of the reason the send did not complete. Review Email Send Error Codes for more information.
StatusMessage	String		The reason the send did not complete. Review Email Send Error Codes for more information.
EventCategoryType	String		TransactionalSendEvent event type. EmailSent indicates that the email was sent to the email provider. EmailNotSent indicates that the email was not sent and includes the reason. EmailQueued indicates that the email has not yet been processed for sending.
Timestamp	String		Time the event occured in Central Standard Time (no daylight savings)
DefinitionKey	String		Unique identifier of the definition.
EventId	Integer		Numeric identifier of the event.
MessageKey	String		Unique identifier to track message send status.
ContactKey	String		Unique identifier for a subscriber in Marketing Cloud.
To	String		Channel address of the recipient. For email, it’s the recipient's email address. For SMS, it’s the recipient's mobile number.

Stored procedures are function-like interfaces that extend the functionality of the Cloud 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.

CData Cloud - Salesforce Marketing Cloud Stored Procedures

Name	Description
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.
CreateKeyword	Creates a keyword on an account.
CreateMessageListDeliveryReport	Triggers a delivery report for messageList API.
CreateOptInMessage	Creates an SMS opt-in message permitting contacts to subscribe to further SMS messages.
CreateTriggeredSend	Create a triggered send object which represents a specific instance of a triggered email send.
DeleteKeyword	Deletes a keyword on an account.
FireEntryEvent	Fire the entry event that initiates the journey.
GetChannelViewHtml	Returns the requested channel view's compiled HTML for the asset.
GetDeliveryStatusOfQueuedMO	Retrieves the delivery status of a queued MO.
GetFileForAnAsset	Gets the binary file for an asset.
GetHeaderFooterAccount	Gets the default header and footer for an account.
GetHeaderFooterEmail	Gets the header and footer for an email.
GetImportSendStatus	Retrieves the status of a ImportSend automation.
GetImportStatus	Retrieves the status of an import job.
GetJourneyPublicationStatus	Retrieves the status of a journey publication.
GetMessageContactHistory	Retrieves the last message sent to a mobile number.
GetMessageContactStatus	Retrieves the overall delivery status of a message to a contact.
GetMessageListStatus	Returns status for a message sent to a group of mobile numbers.
GetMessageSendStatus	Gets the send status for a message.
GetRefreshListStatus	Retrieves the refresh list status.
GetSubscriptionStatus	Returns subscription status for mobile numbers or subscriber keys.
GetTrackingHistoryOfQueuedMO	Retrieves the tracking history of a queued MO.
ImportAndSendMessage	Imports and sends.
PostMessageToList	Initiates a message to one or more contact lists.
PostMessageToNumber	Initiates a message to one or more mobile numbers.
PublishJourney	Publishes a journey version asynchronously.
QueueContactImport	Queues a contact import.
QueueMoMessage	Queues an MO message for send.
RefreshList	Refreshes a list.
SendMessageToRecipient	Send an OTT message to the recipient. Supported OTT networks are Facebook Messenger and LINE.
SendTransactionalMessageToMultipleRecipients	Sends a message to a single recipient via a send definition.
SendTransactionalMessageToRecipient	Sends a message to a single recipient via a send definition.
StopJourney	Stop a running journey.

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

Name	Type	Required	Description
TokenId	String	True	The ID provided in the MessageList REST response.
FileName	String	True	The name of the report file in your FTP/reports folder.

Result Set Columns

Name	Type	Description
Success	Boolean	Whether the csv file was successfully generated or not.

Creates a keyword on an account.

Input

Name	Type	Required	Description
LongCode	String	False	The long code that the keyword will be created on.
ShortCode	String	False	The short code that the keyword will be created on.
Keyword	String	False	The keyword that will be created.
CountryCode	String	False	The two letter country code specifying the country that short code belongs to.

Result Set Columns

Name	Type	Description
KeywordId	String	The Id of the created keyword.

Triggers a delivery report for messageList API.

Input

Name	Type	Required	Description
TokenId	String	True	The ID provided in the MessageList REST response.
MessageId	String	True	The API key of the message definition (configured in the MobileConnect user interface).
FileName	String	True	The name of the report file in your FTP/reports folder.

Result Set Columns

Name	Type	Description
Success	Boolean	Whether the delivery report was successfully triggered or not.

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

Input

Name	Type	Required	Description
LongCode	String	False	Long code that the message will be created on. Either long code or short code is required.
ShortCode	String	False	Short code that the message will be created on. Either long code or short code is required.
MessageName	String	True	Name of the message.
MessageText	String	False	Text of the message.
CountryCode	String	False	Two-letter country code specifying the country that the short code belongs to. Required for short code.
Keyword	String	True	Keyword to which users are subscribed
MessageOptInType	String	True	Opt-in template: Single, Double, or Age. Single opt-in requires users to text one keyword (e.g., JOIN) to opt in. Double opt-in requires users to text in a second keyword (e.g., Y or YES) to confirm opt in. Age means double opt-in with age confirmation. It requires users to text in their age after texting JOIN. If age is above the minimum, the user is opted in.
ResponseMessage	String	False	Response sent to users after they text in the keyword for Single. Required for Single. Not used for Double or Age.
DoubleOptInInitialMessage	String	False	Response sent to users that asks for confirmation of opt-in (for Double) or for their age (for Age). Required for Double and Age. Not used for Single.
DoubleOptInConfirmationMessage	String	False	Response sent to users after they text Y or YES for Double. Required for Double or Age. Not used for Single.
DoubleOptInValidResponses	String	False	Valid responses that users can text in for Double, such as Y or YES. Required for Double. Not used for Single or Age.
OptInInvalidAgeMessage	String	False	Response sent to users whose age is below the minimum. Required for Age. Not used for Single or Double.
MinimumAge	Integer	False	Minimum age that users can text in and be opted in. Required for Age. Not used for Single or Double.
AllowSingleOptIn	Boolean	False	Determines whether users receive a different response if they are already opted in: True or False.
DuplicateOptInMessage	String	False	Response sent to users who are already opted in.
OptinErrorMessage	String	True	Response sent to users if an error occurred while opting them in.
StartDate	Date	True	Date the message becomes active.
EndDate	Date	True	Date the message becomes inactive. If users try to opt in to an inactive message, they receive the default keyword response for the code or the new message that the keyword has been set up for.
NextKeyword	String	False	Keyword to append to the next message received from users. For example, the NextKeyword is ZIP. After a user texts in JOIN, the response is 'Welcome. Please send your zip code.' Users can text in their zip code without using the keyword ZIP.

Result Set Columns

Name	Type	Description
MessageID	String	The Id of the created opt-in message.

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='[email protected]', FromName='test', ToAddress='[email protected]',SubscriberKey='12345678', Subscribers='Subscribers#Temp'

Execute

you can execute the stored procedure.

EXECUTE CreateTriggeredSend  key='TEST_1', FromAddress='[email protected]', FromName='test', ToAddress='[email protected]',SubscriberKey='12345678'

EXECUTE CreateTriggeredSend  key='TEST_1', FromAddress='[email protected]', FromName='test', ToAddress='[email protected]', SubscriberKey='12345678', SubscriberAttributes='{\"attrname\":\"test\",\"attrname2\":22,\"attrname3\":\"testing\"}'

Input

Name	Type	Required	Description
Key	String	False	Defines associated triggered send definition for triggered send. This property corresponds to the external key assigned to an object in Marketing Cloud.
SendId	String	False	ID of the entry event send definition that comes from the response when creating a TriggeredSendDefinition. Either this or the external key is required.
FromAddress	String	False	Email address that will show as the sender.
FromName	String	False	Name of the sender.
ToAddress	String	True	Email address of the message recipient.
SubscriberKey	String	True	Unique identifier defined for the message recipient.
SubscriberAttributes	String	False	Name and value pairs for subscriber attributes defined for the message.
RequestType	String	False	Valid values ASYNC and SYNC. Default value when not defined is ASYNC. Determines if the request is processed asychronously.

Result Set Columns

Name	Type	Description
Success	String	Returns True if triggered is created successfully otherwise return false.
RecipientSendId	String	Recipient Send Id generated when event send definition triggered successfully.
Messages	String	Defines the detailed description of the messages.

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

Name	Type	Required	Description
KeywordId	String	False	Encoded Id of keyword that will be deleted
Keyword	String	False	The keyword that will be deleted.
LongCode	String	False	The long code of the keyword that will be deleted
ShortCode	String	False	The short code of the keyword that will be deleted.
CountryCode	String	False	The two letter country code specifying the country that short code belongs to.

Result Set Columns

Name	Type	Description
Status	String	The current status after the delete operation.

Fire the entry event that initiates the journey.

Input

Name	Type	Required	Description
ContactKey	String	True	The ID that uniquely identifies a subscriber/contact.
EventDefinitionKey	String	True	Find the EventDefinitionKey in Event Administration after the event is created and saved. This is true for both standard and custom events. Do not include a period in the event definition key.
Data	String	False	Properties of the event. Only required if defined in a custom event or by the event.

Result Set Columns

Name	Type	Description
EventInstanceId	String	The instance id of the entry event.

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

Input

Name	Type	Required	Description
AssetId	Integer	True	The ID of the asset.
ViewName	String	True	The name of the asset view to retrieve.
Thumbnail	Boolean	False	Set to true to return a base64-encoded thumbnail image.
IncludeHeaderFooter	Boolean	False	If asset has HTML or text views, set to true to include the message header and footer.
IncludeDesignContent	Boolean	False	Set to true to include design content in the thumbnail image.
DownloadPath	String	False	The Location of the file in which to save the compiled HTML.
Encoding	String	False	The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.

Result Set Columns

Name	Type	Description
Success	Boolean	Returns True if the download succeeded.
Compiled	String	A representation of the view.

Retrieves the delivery status of a queued MO.

Input

Name	Type	Required	Description
TokenId	String	True	Token Id returned for the queued MO.

Result Set Columns

Name	Type	Description
Tracking	String	The tracking history of the queued MO.

Gets the binary file for an asset.

Input

Name	Type	Required	Description
AssetId	Integer	True	The ID of the asset.
DownloadPath	String	False	The Location where the file will be stored.

Result Set Columns

Name	Type	Description
Success	Boolean	Returns True if the download succeeded.
Content	String	A Base64-encoded string of the file.

Gets the default header and footer for an account.

Result Set Columns

Name	Type	Description
HTMLHeader	String	The HTML header of the salutation.
HTMLFooter	String	The HTML footer of the salutation.
TextHeader	String	The text header of the salutation.
TextFooter	String	The text footer of the salutation.

Gets the header and footer for an email.

Input

Name	Type	Required	Description
AssetId	Integer	True	The ID of the asset.

Result Set Columns

Name	Type	Description
HTMLHeader	String	The HTML header of the email.
HTMLFooter	String	The HTML footer of the email.
TextHeader	String	The text header of the email.
TextFooter	String	The text footer of the email.

Retrieves the status of a ImportSend automation.

Input

Name	Type	Required	Description
TokenID	String	True	The ID provided in the ImportAndSend SP.

Result Set Columns

Name	Type	Description
Status	String	The status of the ImportSend automation.
LastUpdate	Datetime	The last date when the ImportSend automation was updated.
CreatedTime	Datetime	The date when the ImportSend automation was created.
StartTime	Datetime	The date when the ImportSend automation started.
CompletedTime	Datetime	The date when the ImportSend automation finished.
LastRunTime	Datetime	The last date when the ImportSend automation was running.
Source	String	The source of the ImportSend automation.
Inserted	Integer	Number of new records in _MobileSubscription.
Updated	Integer	Number of updated records.
Invalid	Integer	Number of invalid rows in the import file.

Retrieves the status of an import job.

Input

Name	Type	Required	Description
ListID	String	True	The ID of the List found in the MobileConnect interface.
TokenID	String	True	The unique ID returned when using the ImportQueue operation.

Result Set Columns

Name	Type	Description
Status	String	The message text sent in the SMS message.

Retrieves the status of a journey publication.

Input

Name	Type	Required	Description
StatusId	String	True	The status ID of a journey publication.

Result Set Columns

Name	Type	Description
Status	String	The publishing status for the specified statusId.

Retrieves the last message sent to a mobile number.

Input

Name	Type	Required	Description
MessageId	String	True	Message Id provided for the messageContact.
TokenId	String	True	Token Id returned for the messageContact.
MobileNumber	String	True	Mobile number for the messageContact.

Result Set Columns

Name	Type	Description
Count	Integer	The total of mobile numbers included in the send request subtracting the number of unsubscribed recipients.
CreateDate	Datetime	Date when the MessageContact send was submitted.
Status	String	Delivery status of a message to a contact.
History	String	History information related to the last message sent to a mobile number.

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

Input

Name	Type	Required	Description
MessageId	String	True	Message Id provided for the messageContact.
TokenId	String	True	Token Id returned for the messageContact.

Result Set Columns

Name	Type	Description
Message	String	The message text sent in the SMS message.
Count	Integer	The total of mobile numbers included in the send request subtracting the number of unsubscribed recipients.
CreateDate	Datetime	Date when the MessageContact send was submitted.
CompleteDate	Datetime	Date when the send completed.
Status	String	Delivery status of a message to a contact.
Tracking	String	Tracking information related to the delivery status of a message to a contact.

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

Input

Name	Type	Required	Description
MessageId	String	True	Message Id provided for the messageList.
TokenId	String	True	Token Id returned for the messageList.

Result Set Columns

Name	Type	Description
Message	String	The message text sent in the SMS message.
Count	Integer	The total of mobile numbers included in the send request subtracting the number of unsubscribed recipients.
CreateDate	Datetime	Date when the MessageList send was submitted.
CompleteDate	Datetime	Date when the send completed.
Status	String	Delivery status of a message to a group of mobile numbers.
Tracking	String	Tracking information related to the delivery status of a message to a group of mobile numbers.

Gets the send status for a message.

EXECUTE GetMessageSendStatus MessageKey = 'bcX0qaEp0USGciEnUJTW0w'

Input

Name	Type	Required	Description
MessageKey	String	True	Unique identifier to track message send status.

Result Set Columns

Name	Type	Description
RequestId	String	The unique identifier of this request.
EventCategoryType	String	TransactionalSendEvents event type.
Timestamp	String	Time the event occured in Central Standard Time.
CompositeId	String	Marketing Cloud-specific processing identifier that can be referenced for support.

Retrieves the refresh list status.

Input

Name	Type	Required	Description
ListId	String	True	The ID of the list found in the MobileConnect interface
TokenId	String	True	The unique ID returned when using the RefreshList operation

Result Set Columns

Name	Type	Description
Status	String	The status of the refresh list.

Returns subscription status for mobile numbers or subscriber keys.

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

Input

Name	Type	Required	Description
MobileNumbers	String	False	An array of mobile numbers.
SubscriberKeys	String	False	An array of subscriber keys.

Result Set Columns

Name	Type	Description
Contacts	String	Information about the subscription status for mobile numbers or subscriber keys.

Retrieves the tracking history of a queued MO.

Input

Name	Type	Required	Description
TokenId	String	True	Token Id returned for the queued MO.

Result Set Columns

Name	Type	Description
Status	String	The status of the refresh list.
History	String	History information related to the queued MO.

Imports and sends.

EXECUTE ImportAndSendMessage MessageId = 'MessageId', " +
          "Keyword = 'Test_Keyword'," +
          "NotificationEmail = '[email protected]'," +
          "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

Name	Type	Required	Description
MessageId	String	True	Encoded message Id.
Keyword	String	True	A valid keyword on the shortcode for the message to opt the numbers on to.
NotificationEmail	String	False	If specified, email notifications will be sent on import and program completion.
Override	Boolean	False	Flag to indicate whether the override text should be used.
OverrideText	String	False	Text to override the existing message.
IsDuplicationAllowed	Boolean	False	If true, duplicate messages may be sent.
IsVisible	Boolean	False	If specified true, the import definition and list created will be visible.
ImportDefinition	String	True	List of Import Definitions to be created (currently limited to 1).

Result Set Columns

Name	Type	Description
TokenId	String	The token Id.
LastPublishDate	String	The last published date.

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

Name	Type	Required	Description
MessageId	String	True	The encodedID can be found when creating a 'API Entry Event' type Outbound message in the UI. If you have already passed that point you can find the ID by looking at the API resource behind the scenes when you open that message in the UI.
TargetListIds	String	False	A list of one or more List ID strings. The contacts in these Lists will be included in the send and will overwrite the Message's default inclusion lists.
OverrideTemplateTargetLists	Boolean	False	A flag indicating TargetListIds will be provided for overriding the message default Target List Ids.
ExclusionListIds	String	False	A list of one or more List ID strings. The contacts in these Lists will be excluded in the send and will overwrite the Message's default exclusion lists.
OverrideTemplateExclusionLists	Boolean	False	A flag indicating ExclusionListIds will be provided for overriding the message default Exclusion List Ids.
IgnoreExclusionLists	Boolean	False	A flag indicating that exclusion lists (even the default message exclusion lists) will not be used in the send.
OverrideMessageText	Boolean	False	A flag indicating that the text provided should override the text stored with the Message.
MessageText	String	False	Required when OverrideMessageText is true.
UtcOffset	String	False	The UTC offset of the blackout window start and end times. UtcOffset is required in every REST call in order for the blackout window to be honored.
WindowStart	String	False	The start time of the blackout window, in the UTC offset specified. To see if the SendTime is within the blackout window, convert the WindowStart and WindowEnd times to UTC and compare them to the SendTime.
WindowEnd	String	False	The end time of the blackout window, in the UTC offset specified. To see if the SendTime is within the blackout window, convert the WindowStart and WindowEnd times to UTC and compare them to the SendTime.
SendTime	Datetime	False	Date and Time in UTC that the message will go out. Example format: 2012-10-17 17:01. The BlackoutWindow will still be respected if used with this option. If the SendTime is set to a date and time in the past, the message will send immediately.
AllowDuplication	Boolean	False	The same mobile number may receive multiple texts if this value is true.
ContentURL	String	False	The URL of the media content sent via an MMS message.

Result Set Columns

Name	Type	Description
TokenId	String	A token that can be used to make a follow-up call to check the status of the request.

Initiates a message to one or more mobile numbers.

Subscribers

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

Column	Description
MobileNumber	Specifies the mobile number used as the unique identifier for that record.
SubscriberKey	Specifies the SubscriberKey value used as the unique identifier for that record.
Attributes	Set real-time attributes for individual personalization strings, per subscriber. The subscriber attribute must match the attribute string in the message. You can pass attributes that are not used as attributes in the message into the SMS send log.

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

Name	Type	Required	Description
MessageId	String	True	The encoded message ID.
MobileNumbers	String	False	An array of one or more mobile numbers.
Subscribe	Boolean	False	Flag to indicate a subscription should be created if none exist.
Resubscribe	Boolean	False	Flag to indicate a subscription should be reset if currently unsubscribed.
Keyword	String	False	The keyword must align with code on message. Required when subscribe and/or resubscribe are true.
Override	Boolean	False	Flag to indicate that the contact has received the messageText as provided instead of the message's original text.
MessageText	String	False	Text value to be used in place of the message's original text. This value is required when override is true.
UtcOffset	String	False	The UTC offset of the blackout window start and end times. UtcOffset is required in every REST call in order for the blackout window to be honored.
WindowStart	String	False	The start time of the blackout window, in the UTC offset specified. To see if the SendTime is within the blackout window, convert the WindowStart and WindowEnd times to UTC and compare them to the SendTime.
WindowEnd	String	False	The end time of the blackout window, in the UTC offset specified. To see if the SendTime is within the blackout window, convert the WindowStart and WindowEnd times to UTC and compare them to the SendTime.
SendTime	Date	False	Date and Time in UTC that the message will go out. Example format: 2012-10-17 17:01. The BlackoutWindow will still be respected if used with this option. If the SendTime is set to a date and time in the past, the message will send immediately.
ContentURL	String	False	The URL of the media content sent via an MMS message.

Result Set Columns

Name	Type	Description
TokenId	String	A token that can be used to make a follow-up call to check the status of the request.

Publishes a journey version asynchronously.

Input

Name	Type	Required	Description
JourneyId	String	True	The ID of the journey to publish expressed in the form of a GUID (UUID).
JourneyVersion	Integer	True	Version number of the journey to publish.

Result Set Columns

Name	Type	Description
StatusId	String	The status ID of a journey publication.

Queues a contact import.

FieldMaps

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

Column	Description
Destination	Destination field map.
Ordinal	Ordinal field map.
Source	Source field map.

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 = '[email protected]', " +
          "ImportMappingType = 'MapByOrdinal', FileName = 'testdata.csv', FileType = 'csv', IsFirstRowHeader = true

Input

Name	Type	Required	Description
ListId	String	True	The list id.
ShortCode	String	False	The short code.
Keyword	String	False	The keyword.
SendEmailNotification	Boolean	False	Send email notification.
EmailAddress	String	False	Email address the notification goes to.
ImportMappingType	String	False	Field mapping type.
FileName	String	False	The name of the file, including extensions.
FileType	String	False	The only type supported is csv.
IsFirstRowHeader	Boolean	False	Flag indicating whether or not the first row is the header.

Result Set Columns

Name	Type	Description
TokenId	String	The token Id of the queued contact import.

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

Name	Type	Required	Description
MobileNumbers	String	False	An array of mobile numbers used in the send. Either the mobileNumbers or subscribers property is required, but not both.
Subscribers	String	False	An array of subscriber keys and mobile numbers used in the send. Either the mobileNumbers or subscribers property is required, but not both.
ShortCode	String	True	The short code.
MessageText	String	True	The text value.

Result Set Columns

Name	Type	Description
Results	String	Results related to the queue process of an MO message for send.

Refreshes a list.

Input

Name	Type	Required	Description
ListId	String	True	The Id of the list to refresh.

Result Set Columns

Name	Type	Description
TokenId	String	The token Id which can be used to check the status of the request.

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:

Column	Description
Type	Indicates the message content type of the send request with values: text, image, audio, video, native.
Text	Message text to be sent out to the recipient. Required for 'text' type message content.
Url	URL of the multimedia attachment to be sent out to the recipient. Required for 'image, audio, video' type message content.
AltUrl	Alternate URL of the multimedia attachment to be sent out to the recipient. Required for LINE for 'image, video' type message content.
Duration	Length of the audio multimedia attachment to be sent out to the recipient. Required for LINE for 'audio' type message content.
NativePayload	Ott-network-specific blob of JSON payload passed in message request.
IsReusable	Indicates if a multimedia attachment can be reused for future messages. Only supported for Messenger.
AttachmentId	Attachment Id of a reusable multimedia asset. Only supported for Messenger.

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:

Column	Description
messagingType	Indicates the messaging_type of a messenger send request with values: RESPONSE, UPDATE, MESSAGE_TAG. Required for Messenger send requests.
tag	Message Tag of a messenger send request. Required for Messenger send request if messagingType = Message_TAG.
notificationType	Indicates the push notification type for message send request with values: REGULAR, SILENT_PUSH, NO_PUSH. Required for Messenger. Optional for Messenger send requests.

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

Name	Type	Required	Description
MessageKey	String	True	User-defined message identifier.
MessageGroupKey	String	False	User-defined message identifier to group a number of send requests.
SenderType	String	True	Indicates the name of the OTT network the resource is being registered with values: line, messenger.
SenderId	String	True	Indicates the identifier of the OTT resource sending the message: LINE Channel Id, Messenger Page Id.
OttId	String	True	Recipient ID of the user. For LINE, if userReference is invalid, a retry is attempted with ottId as senderId.
UserReference	String	True	Alternate Recipient ID of the user. For Messenger: user_ref can be passed as userReference. For LINE, reply_token can be passed as userReference.
Subject	String	False	Message name.
ValidityPeriod	Integer	True	Time period for which the request is valid.

Result Set Columns

Name	Type	Description
OttRequestId	String	The request ID of the OTT.

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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', 'GV1LhQ6NFkqFUAE1IsoQ9Q', '{"UserAttribute_3":"value_3", "UserAttribute_4":"value_4"}');


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

Input

Name	Type	Required	Description
DefinitionKey	String	True	Unique identifier of the definition.
RecipientContactKey	String	False	Unique identifier for a subscriber in Marketing Cloud. Each request must include a contactKey. You can use an existing subscriber key or create one at send time by using the recipient’s email address.
RecipientTo	String	False	Channel address of the recipient. For email, it’s the recipient's email address.
RecipientMessageKey	String	False	Unique identifier used to track message status. Can be automatically created when you create a message or provided as part of the request. Can be up to 100 characters, and there are no restricted characters. Each recipient in a request must have a unique messageKey. If you use a duplicate messageKey in the same send request, the message is rejected.
RecipientAttributes	String	False	Information used to personalize the message for the recipient. Written as key pairs. The attributes match profile attributes, content attributes, or triggered send data extension attributes.
RecipientAggregate	String	True	Array of recipient objects that contain parameters and metadata for the recipients, such as send tracking and personalization attributes.
Attributes	String	False	Information used to personalize the message for the recipient. Written as key pairs. The attributes match profile attributes, content attributes, or triggered send data extension attributes.

Result Set Columns

Name	Type	Description
RequestId	String	The unique identifier of this request.
MessageKey	String	Unique identifier to track message send status.
Status	String	The status of the send request.
ErrorMessage	String	The error message.

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 = '[email protected]', 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 = '[email protected]', Attributes = 'Attributes#TEMP';

Input

Name	Type	Required	Description
MessageKey	String	True	User-defined message identifier.
DefinitionKey	String	True	Unique identifier of the definition.
RecipientContactKey	String	True	Unique identifier for a subscriber in Marketing Cloud. Each request must include a contactKey. You can use an existing subscriber key or create one at send time by using the recipient's email address.
RecipientTo	String	False	Channel address of the recipient. For email, it's the recipient's email address.
Attributes	String	False	Information used to personalize the message for the recipient. Written as key pairs. The attributes match profile attributes, content attributes, or triggered send data extension attributes.

Result Set Columns

Name	Type	Description
RequestId	String	The unique identifier of this request.

Stop a running journey.

Input

Name	Type	Required	Description
JourneyId	String	True	The ID of the journey to stop, expressed in the form of a GUID (UUID).
JourneyVersion	Integer	True	The version number of the journey to stop.

Result Set Columns

Name	Type	Description
Success	Boolean	Whether the journey was stopped.

The Cloud 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.

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

CData Cloud - Salesforce Marketing Cloud Tables

Name	Description
Account	A Marketing Cloud account.
AccountUser	An individual user within an account. This table does not support deletes.
BusinessUnit	A unit within a larger Enterprise or Enterprise 2.0 account. This table supports queries and updates only.
ContentArea	A ContentArea represents a defined section of reusable content.
DataExtension	Represents a data extension within an account.
Email	Represents an email in a Marketing Cloud account.
EmailSendDefinition	Record that contains the message information, sender profile, delivery profile, and audience information.
FileTrigger	Reserved for future use. This table does not suport deletes.
FilterDefinition	Defines an audience based on specified rules in a filter. This table does not support inserts.
ImportDefinition	Defines a reusable pattern of import options. This table does not support inserts.
List	A marketing list of subscribers.
Portfolio	Indicates a file within the Portfolio of a Marketing Cloud account.
ProgramManifestTemplate	Reserved for future use. This table does not support deletes or inserts.
QueryDefinition	Represents a SQL query activity accessed and performed by the SOAP API. This table does not support updates or inserts.
ReplyMailManagementConfiguration	Details configuration settings for the reply mail management in an account. This table does not support deletes.
Send	Used to send email and retrieve aggregate data. This table does not support deletes or updates.
SendClassification	Represents a send classification in a Marketing Cloud account.
SenderProfile	The send profile used in conjunction with an email send definition.
SMSTriggeredSend	Indicates a single instance of an SMS triggered send. This table does not support deletes or updates.
Subscriber	A person subscribed to receive email or SMS communication.
SuppressionListDefinition	A suppression list that can be associated with different contexts.
TriggeredSendDefinition	To create or update a TriggeredSendDefinition where the list ID is the All Subs List ID, you need the Email | Subscribers | All Subscribers | View and SendEmailToList permissions.

A Marketing Cloud account.

Table-Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud.

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

Name	Type	ReadOnly	Description
ID [KEY]	Int	False	Identifier of the account.
AccountType	String	False	Type of Marketing Cloud account. The allowed values are BUSINESS_UNIT, CHANNEL_CONNECT, CONNECT, DOTO_MEMBER, ENTERPRISE_2, EXACTTARGET, LP_MEMBER, None, PRO_CONNECT, PRO_CONNECT_CLIENT.
ParentID	Int	False	Specifies the ID number of the parent account for Lock and Publish, On Your Behalf, Enterprise, and Enterprise 2.0 account children and business units.
BrandID	Int	False	Specifies brand tags to use on an account.
PrivateLabelID	Int	False	Specifies the private label for an account.
ReportingParentID	Int	False	Reserved for future use.
Name	String	False	Name of the account.
Email	String	False	Default email address the account. Indicates if subscriber information can be used for email sends.
FromName	String	False	Specifies the default email message From Name. Deprecated for email send definitions and triggered send definitions.
BusinessName	String	False	Business name of an account's owner.
Phone	String	False	Specifies a phone number.
Address	String	False	The address used to communicate with a Person.
Fax	String	False	Fax number of the account's owner.
City	String	False	City of an account's owner to be displayed in the physical mailing address required at the bottom of all email messages.
State	String	False	Specifies the geographical state of the account's owner.
Zip	String	False	Specifies the zip code of the account's owner.
Country	String	False	Country of an account's owner, as displayed in the physical mailing address required at the bottom of all email messages.
IsActive	Boolean	False	Specifies whether or not the account is active.
IsTestAccount	Bool	False	Specifies whether or not an account is a 'Test' account.
Client_ClientID1	Int	True	The Client Id of the client.
DBID	Int	False	Reserved for future use.
CustomerID	Long	False	Reserved for future use.
DeletedDate	Datetime	True	Date and time of an account's deletion.
EditionID	Int	False	Specifies the product edition of the account.
ModifiedDate	Datetime	False	Indicates the last time account information was modified.
CreatedDate	Datetime	False	Date and time of the account's creation.
ParentName	String	False	Specifies the name of the Parent account.
Subscription_SubscriptionID	String	True	Reserved for future use.
Subscription_HasPurchasedEmails	Bool	True	Reserved for future use.
Subscription_EmailsPurchased	Int	True	Specifies the number of emails purchased in a subscription.
Subscription_Period	String	True	Reserved for future use.
Subscription_AccountsPurchased	Int	True	Marketing Cloud Accounts purchased.
Subscription_LPAccountsPurchased	Int	True	Specifies the number of Lock and Publish account purchased.
Subscription_DOTOAccountsPurchased	Int	True	Specifies number of Marketing Cloud agency reseller accounts purchased.
Subscription_BUAccountsPurchased	Int	True	Defines the number of business units purchased for a subscription.
Subscription_AdvAccountsPurchased	Int	True	This property represents the number of advertising accounts purchased for the account.
Subscription_BeginDate	Datetime	True	Specifies the date a subscription begins.
Subscription_EndDate	Datetime	True	Specifies the end data of an activity.
Subscription_Notes	String	True	Deprecated.
PartnerKey	String	False	Unique identifier provided by partner for an account, accessible only via API.
Client_PartnerClientKey	String	True	The partner client key of the client.
InheritAddress	Bool	False	Specifies that an Enterprise 2.0 business unit will inherit the address from the parent business unit.
UnsubscribeBehavior	Int	True	The behavior of the subscription when unsubscripbed.
Subscription_ContractNumber	String	True	Reserved for future use.
Subscription_ContractModifier	String	True	Reserved for future use.
IsTrialAccount	Bool	False	Reserved for future use.
Client_EnterpriseID	Long	True	Read-only identifier the enterprise of the client.
ParentAccount_ID	Int	False	Read-only identifier for the parent of the account.
ParentAccount_Name	String	True	Name of the parent of the account.
ParentAccount_ParentID	Int	True	Read-only identifier for the parent of the parent of this account.
ParentAccount_CustomerKey	String	True	The customer key of the parent account.
ParentAccount_AccountType	String	True	The account type of the parent account.
CustomerKey	String	False	User-supplied unique identifier for an object within an object type.
Locale_LocaleCode	String	True	The locale code of the locale.
TimeZone_ID	Int	True	Read-only identifier of the timezone.
TimeZone_Name	String	True	Name of the timezone.
Roles	String	False	Collection of roles defined for an account.
ContextualRoles	Int	True	The contextual roles of the account.
ObjectState	String	False	Reserved for future use.
LanguageLocale_LocaleCode	String	True	The locale code of the language layout.
IndustryCode	String	False	The code of the industry.
AccountState	Int	False	The state of the account.
SubscriptionRestrictionFlags	Long	False	Restriction flags of the subscription.

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

Table-Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud.

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', '[email protected]', '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

Name	Type	ReadOnly	Description
ID [KEY]	Int	False	Identifier for an object.
CreatedDate	Datetime	False	Date and time of the object's creation..
ModifiedDate	Datetime	False	Indicates the last time object information was modified.
Client_ID [KEY]	Int	False	The Id of the client.
AccountUserID	Int	False	Specifies the Marketing Cloud identifier of an account user.
UserID	String	False	The Id of the user.
Name	String	False	Name of the object or property.
Email	String	False	Default email address for object. Indicates if subscriber information can be used for email sends.
MustChangePassword	Bool	False	Indicates whether user must change password on next login.
ActiveFlag	Bool	False	Specifies the status of an account user.
ChallengePhrase	String	False	Specifies the challenge answer for login assistance.
ChallengeAnswer	String	False	Specifies the challenge answer for login assistance.
IsAPIUser	Bool	False	Indicates if a user can use the API. A value of true indicates the user's password remains the same until actively changed.
NotificationEmailAddress	String	False	Indicates email address to which to send notifications.
Client_PartnerClientKey	String	False	The partner client key of the partner.
Password	String	False	Specified the password of an account user.
Locale_LocaleCode	String	True	The locale code of the locale.
TimeZone_ID	Int	True	The id of the timezone.
TimeZone_Name	String	True	The name of the timezone.
CustomerKey	String	False	User-supplied unique identifier for an object within an object type.
DefaultBusinessUnit	Int	False	Indicates business unit account user initially accesses.
LanguageLocale_LocaleCode	String	True	The locale code of the language locale.
Client_ModifiedBy	Int	False	Returns user ID for user who modified the object.