Salesforce Pardot v2 Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8858 of the connector.

Get Started¶

Salesforce Marketing Cloud Account Engagement Version Support

The connector leverages the Salesforce Marketing Cloud Account Engagement API to enable bidirectional access to Salesforce Marketing Cloud Account Engagement. Supports Marketing Cloud Account Engagement API v3, v4, and v5.

Establish a Connection¶

Connect to Pardot¶

Connect using OAuth2¶

Set the AuthScheme to OAuth to authenticate with this method.

Pardot Business Unit ID¶

To connect via OAuth2.0 you need to obtain the BusinessUnitId.

To find the Pardot Business Unit ID, use Setup in Salesforce.

• From Setup, enter "Pardot Account Setup" in the Quick Find box.

Your Pardot Business Unit ID begins with "0Uv" and is 18 characters long. If you cannot access the Pardot Account Setup information, ask your Salesforce Administrator to provide you with the Pardot Business Unit ID.

You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.

See Using OAuth Authentication for an authentication guide and details of the OAuth authentication flow.

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

If you do not have access to the user name and password or do not wish to require them, you can use OAuth authentication. Salesforce Marketing Cloud Account Engagement uses the OAuth authentication standard, which requires the authenticating user to interact with Salesforce Marketing Cloud Account Engagement via the browser. The connector facilitates the OAuth exchange in various ways, as described in this section.

Create an OAuth Application¶

To obtain the OAuth client credentials, consumer key, and consumer secret:

1. Log in to salesforce.com.

2. From Setup, enter Apps in the Quick Find box and then click the link to create an application. In the Connected Apps section of the resulting page, click New.

3. Enter a name to be displayed to users when they log in to grant permissions to your application, along with a contact Email address.

4. Click Enable OAuth Settings and enter a value in the Callback URL box. If you are creating a desktop application, set the Callback URL to http://localhost:33333 or to a different port number of your choice. If you are creating a web application, set the Callback URL to a page on your web application you want the user to be returned to after they have authorized your application.

5. Select the scope of permissions that your application should request from the user. At a minimum, OAuth scopes must include "pardot_api", "api", and "refresh_token" scopes. Adding "refresh_token" causes the API to returen a refresh token in the OAuth request, which the driver then uses to automatically refresh the access token when it expires.

   Use "full" to include all scopes. Otherwise, only the username-password OAuth flow can be used with the Pardot API.

6. Click your application name to open a page with information about your application. The OAuth client credentials, consumer key, and consumer secret are displayed.

Create an OAuthJWT Application¶

To obtain the OAuthJWT consumer key:

1. Log in to salesforce.com.

2. From Setup, enter Apps in the Quick Find field and then click the link to create an application. In the Connected Apps section of the resulting page, click New.

3. Enter a name to be displayed to users when they log in to grant permissions to your application, along with a contact Email address.

4. Click Enable OAuth Settings and enter a value in the Callback URL box. Set this value only to create the Connected application. It is not needed for this type of authentication. The Callback URL is in the format:

   http://localhost:8019/src/oauthCallback.rst
   

5. Enable Use digital signatures.

6. Upload your certificate.

7. Select the scope of permissions that your applicationshould request from the user. At a minium, the selected OAuth scopes must include'"pardot_api" and "api" scopes. Select "full" to include all scopes.

8. Click your application name to open a page with information about your application. The OAuth consumer key is displayed.

Important Notes¶

Configuration Files and Their Paths¶

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

Advanced Features¶

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

User Defined Views

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

SSL Configuration

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

Proxy

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

Query Processing

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

See Query Processing for more information.

User Defined Views¶

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File¶

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Define Views Using DDL Statements¶

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

Create a View¶

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

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

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

Alter a View¶

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

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

The view is then updated in the JSON configuration file.

Drop a View¶

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views¶

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

Work with User Defined Views¶

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration¶

Customize the SSL Configuration¶

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

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

Data Model¶

Overview

This section shows the available API objects and provides more information on executing SQL to Salesforce Marketing Cloud Account Engagement APIs.

Key Features

• The connector models Salesforce Marketing Cloud Account Engagement entities like documents, folders, and groups as relational views, allowing you to write SQL to query Salesforce Marketing Cloud Account Engagement data.
• Stored procedures allow you to execute operations to Salesforce Marketing Cloud Account Engagement, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Salesforce Marketing Cloud Account Engagement account are immediately reflected when using the connector.

Salesforce Pardot V3 & V4 Data Model¶

The Jitterbit Connector for Salesforce Marketing Cloud Account Engagement models Salesforce Marketing Cloud Account Engagement entities as relational Tables, Views, and Stored Procedures. 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¶

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

Jitterbit Connector for Salesforce Marketing Cloud Account Engagement Tables¶

Campaigns¶

Retrieve and modify campaigns.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Campaigns WHERE Id = 43
SELECT * FROM Campaigns WHERE Name = 'Website Tracking'

Insert¶

This table does not have any required attributes when performing an insert.

Columns¶

Pseudo-Columns¶

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

CustomFields¶

Retrieve and modify custom fields.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM CustomFields WHERE Id = 50
SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2019'

Insert¶

The following attributes are required when performing an insert: FieldId, Name.

INSERT INTO CustomFields (FieldId, Name) VALUES (100, 'Example')

Columns¶

ListMemberships¶

Retrieve and modify list memberships.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• ListId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM ListMemberships WHERE Id = 26
SELECT * FROM ListMemberships WHERE ListId = 44
SELECT * FROM ListMemberships WHERE CreatedAt > '01/01/2019'

Insert¶

The following attributes are required when performing an insert: ListId, ProspectId.

INSERT INTO ListMemberships (ListId, ProspectId) VALUES (20104, 3457990)

Get Deleted¶

To retrieve deleted records, a GETDELETED statement can be executed. The GETDELETED statement supports the same server side filters that are supported by the SELECT statement (except ID when using the equality(=) operator), but does not perform any client side filtering.

GETDELETED FROM ListMemberships WHERE ID = 231

Columns¶

Lists¶

Retrieve and modify lists.

Columns¶

Opportunities¶

Retrieve and modify opportunities.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• Probability supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• ProspectEmail supports '='.
• ProspectId supports '='.

For example, the following queries are processed server side:

SELECT * FROM Opportunities WHERE Id = 14
SELECT * FROM Opportunities WHERE ProspectId = 34
SELECT * FROM Opportunities WHERE ProspectEmail = 'example@exampledomain.com'

Insert¶

The following attributes are required when performing an insert: CampaignId, Name, Value, Probability, ProspectEmail, ProspectId.

INSERT INTO Opportunities (CampaignId, Name, Value, Probability, ProspectEmail, ProspectId) VALUES (1, 'Example', 3, 75, 'example@exampledomain.com', 2)

Note: This table cannot be updated if a Salesforce connector is present.

Columns¶

ProspectAccounts¶

Retrieve and modify prospect accounts.

Columns¶

Prospects¶

Retrieve and modify prospects.

Columns¶

Views¶

Views are similar to tables in the way that data is represented; however, views are read-only.

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

Jitterbit Connector for Salesforce Marketing Cloud Account Engagement Views¶

Account¶

Retrieve account info.

Table Specific Information¶

Select¶

The connector will process all filters client side.

Columns¶

CustomRedirects¶

Retrieve and modify custom redirects.

Columns¶

DynamicContents¶

Retrieve dynamic contents.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM DynamicContents WHERE Id = 32
SELECT * FROM DynamicContents WHERE CreatedAt > '02/03/2019 23:11:33'
SELECT * FROM DynamicContents WHERE UpdatedAt > '01/03/2018 23:11:33' AND UpdatedAt <= '02/03/2019 23:11:33'

Columns¶

EmailClicks¶

Retrieve email clicks.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '>', '>='.
• ListEmailId supports '='.
• DripProgramActionId supports '='.
• EmailTemplateId supports '='.
• TrackerRedirectId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM EmailClicks WHERE ListEmailId = 20
SELECT * FROM EmailClicks WHERE DripProgramActionId = 42
SELECT * FROM EmailClicks WHERE EmailTemplateId = 26 AND TrackerRedirectId = 31 AND CreatedAt > '02/03/2019 23:11:33'

Columns¶

Emails¶

Retrieve emails.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '='.

For example, the following query is processed server side:

SELECT * FROM Emails WHERE ID = 10

Columns¶

EmailStats¶

Retrieve email stats.

Table Specific Information¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. To query this table, you need to specify the Id. The rest of the filter is executed client side within the connector.

• Id supports '='.

For example, the following query is processed server side:

SELECT * FROM EmailStats WHERE ID = 10

Columns¶

EmailTemplates¶

Retrieve email templates.

Table Specific Information¶

Select¶

By default only templates connected to OneToOneEmails will be returned.

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '='.

For example, the following queries are processed server side:

SELECT * FROM EmailTemplates WHERE ID = 22

Columns¶

Forms¶

Retrieve forms.

Columns¶

LifecycleHistories¶

Retrieve lifecycle histories.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM LifecycleHistories WHERE Id = 49
SELECT * FROM LifecycleHistories WHERE Id >= 44
SELECT * FROM LifecycleHistories WHERE CreatedAt <= '02/03/2019 23:11:33'

Columns¶

LifecycleStages¶

Retrieve lifecycle stages.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM LifecycleStages WHERE ID = 18

Columns¶

OneToOneEmails¶

Retrieve one to one emails.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '='.

For example, the following queries are processed server side:

SELECT * FROM OneToOneEmails WHERE ID = 11

Columns¶

TagObjects¶

Retrieve tag objects.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• TagId supports '='.
• Type supports '='.
• ObjectId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM TagObjects WHERE Id <= 31
SELECT * FROM TagObjects WHERE TagId = 12
SELECT * FROM TagObjects WHERE CreatedAt <= '02/03/2019 23:11:33'

Columns¶

Tags¶

Retrieve tags.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Tags WHERE Id >= 42
SELECT * FROM Tags WHERE Name = 'criteria'
SELECT * FROM Tags WHERE CreatedAt >= '02/03/2019 23:11:33'

Columns¶

Users¶

Retrieve users.

Columns¶

VisitorActivities¶

Retrieve visitor activities.

Columns¶

Visitors¶

Retrieve visitors.

Columns¶

VisitorsPageViews¶

Retrieve visitors page views.

Columns¶

Visits¶

Retrieve visits.

Columns¶

Stored Procedures¶

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

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

Jitterbit Connector for Salesforce Marketing Cloud Account Engagement Stored Procedures¶

AssignProspect¶

Update the locations for a shipping zone.

Execute¶

EXEC AssignProspect ProspectId = 432, AssignmentType = 'user', AssignTo = 101

EXEC AssignProspect ProspectId = 432, AssignmentType = 'email', AssignTo = 'prospectEmail@host.tld'

Input

Result Set Columns

AssignProspectAccount¶

Update the locations for a shipping zone.

Execute¶

EXEC AssignProspectAccount UserId = 432, ProspectAccountId = 501

Input

Result Set Columns

GetOAuthAccessToken¶

If using a Windows application, set Authmode to App. If using a Web app, set Authmode to Web and specify the Verifier obtained by GetOAuthAuthorizationUrl.

Input¶

Result Set Columns¶

GetOAuthAuthorizationUrl¶

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

Input¶

Result Set Columns¶

RefreshOAuthAccessToken¶

Refreshes the OAuth token or session ID after the session has expired. Your OAuth application must have the refresh_token scope enabled.

Input¶

Result Set Columns¶

SendEmail¶

Update the locations for a shipping zone.

Execute¶

This procedure requires any of the following input combinations:

One of: ProspectId, ProspectEmail or ListId. One of: TemplateId or (TextContent, Name, Subject and (FromEmail, FromName or FromUserId)) Examples:

EXEC SendEmail CampaignId = 20006, ProspectId = 1807618, TemplateId = 13598
EXEC SendEmail CampaignId = 20006, ProspectEmail = 'prospectEmail@host.tld', TextContent = 'content', Name = 'email name', Subject = 'email subject', FromUserId = 24486
EXEC SendEmail CampaignId = 20006, ListId = 20104, TextContent = 'content', Name = 'email name', Subject = 'email subject', FromEmail = 'senderEmail@host.tld', FromName = 'Name Surname'

Input

Result Set Columns

UnAssignProspect¶

Update the locations for a shipping zone.

Execute¶

EXEC UnAssignProspect ProspectId = 432

Input

Result Set Columns

UnDeleteOpportunity¶

Update the locations for a shipping zone.

Execute¶

EXEC UnDeleteOpportunity ID = 600

Input

Result Set Columns

Salesforce Pardot V5 Data Model¶

The Jitterbit Connector for Salesforce Marketing Cloud Account Engagement models Salesforce Marketing Cloud Account Engagement Pardot API v5 objects as relational Tables, Views, and Stored Procedures. 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 a sample of the available standard tables.

Tables¶

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

Jitterbit Connector for Salesforce Marketing Cloud Account Engagement Tables¶

Campaigns¶

Retrieve and modify campaigns.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Campaigns WHERE Id = 50
SELECT * FROM Campaigns WHERE Name = 'campaignName'
SELECT * FROM Campaigns WHERE CreatedAt > '01/01/2022'

GetDeleted¶

To retrieve campaigns that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Campaings

Columns¶

CustomFields¶

Retrieve custom fields metadata.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM CustomFields WHERE Id = 50
SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2022'
SELECT * FROM CustomFields WHERE UpdatedAt < '01/01/2022'

Columns¶

Pseudo-Columns¶

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

CustomRedirects¶

Track links on your website or third-party site with Pardot custom redirects.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM CustomRedirects WHERE Id = 40
SELECT * FROM CustomRedirects WHERE IsDeleted = 'true'

Insert¶

The following attributes are required when performing an insert: Name, CampaignId, DestinationUrl.

INSERT INTO CustomRedirects (Name, CampaignId, DestinationUrl) VALUES ('name', '1', 'https://example.com/content.html')

Update¶

Update CustomRedirects attributes.

UPDATE CustomRedirects SET Name = 'newName', FolderId = '527425' WHERE ID = 24143

Delete

Remove all CustomRedirects objects or one by specifying the ID of the CustomRedirect.

DELETE FROM CustomRedirects WHERE ID = 10003

GetDeleted¶

To retrieve custom redirects that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM CustomRedirects

Columns¶

DynamicContent¶

Retrieve a collection of dynamic content

Columns¶

Emails¶

Query emails to learn how prospects responded to the emails.

Table Specific Information¶

Select¶

The connector uses the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• ProspectId supports '=', '<', '<=', '>', '>='.
• ListEmailId supports '=', '<', '<=', '>', '>='.
• SentAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Emails WHERE Id = 50
SELECT * FROM Emails WHERE ProspectId = 23
SELECT * FROM Emails WHERE SentAt > '01/01/2022'

Columns¶

EmailTemplates¶

Query email templates.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsOneToOneEmail supports '='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM EmailTemplates WHERE Id = 50
SELECT * FROM EmailTemplates WHERE UpdatedAt > '01/01/2022'
SELECT * FROM EmailTemplates WHERE CreatedAt > '01/01/2022'

Insert¶

To insert EmailTemplates the following fields are required:

• CampaignId
• IsAutoResponderEmail
• IsDripEmail
• IsListEmail
• IsOneToOneEmail
• Name
• Subject
• TextMessage
• HtmlMessage
• SenderOptionsAggregate
• TrackerDomainId
• Type

For inserting into this table specifying the SenderOptionsAggregate is required. The input for this field follows principles of deep Inserts. In Deep Insert, you simultaneously create a base object and link it to the related field in the main table.

You may either submit JSON data, or you may create a temporary table for the child table(in this case SenderOptions) and then reference the temporary table in the insert to the base table.

Example: Deep Inserts using JSON¶

To submit JSON data, simply supply the values for the child table in JSON format.

For example, the following JSON adds two sender options in the email template.

[
    {
        "address": "test@test.com",
        "name": "Test",
        "type": "general_user"
    },
    {
        "address": "test2@test2.com",
        "name": "Test2",
        "type": "general_user"
    }
]

In order to execute the insert, simply reference or include as string literals the complete JSON. For example:

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., '[{"address":"test@test.com","name":"Test","type":"general_user"},{"address":"test2@test2.com","name":"Test2","type":"general_user"}]')

Example: Deep Inserts using Temporary Tables¶

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.

Creating Temporary Tables

Insert the child table fields into temporary table.

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address1')

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address2')

Inserting the email template

In the INSERT statement for the EmailTemplates table, reference the temporary tables in the SenderOptionsAggregate input.

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., 'SenderOptions#TEMP')

Update¶

Updating EmailTemplates:

UPDATE EmailTemplates SET TextMessage = 'newMessage' WHERE ID = 24143

Delete

Remove all email templates or a email template by specifying the ID of the template.

DELETE FROM EmailTemplates WHERE ID = 10003

GetDeleted¶

To retrieve email templates that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM EmailTemplates

Columns¶

Pseudo-Columns¶

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

Files¶

Upload images and other files to use in your Pardot emails, forms, and landing pages.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Files WHERE Id = 40
SELECT * FROM Files WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'

Insert¶

The following attributes are required when performing an insert: Name, FilePath.

INSERT INTO Files (Name, FilePath) VALUES ('file1', 'C:\file1.txt')

Update¶

Updating Files:

UPDATE Files SET Name = 'testingUpdate', FolderId = '33609' WHERE ID = '804969'

Delete

Remove all files or a file by specifying the ID of the file.

DELETE FROM Files WHERE ID = 10003

Columns¶

Pseudo-Columns¶

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

FolderContents¶

View all objects contained within Folders.

Columns¶

Folders¶

Retrieve folders or organize and nest your content and marketing assets in a way that makes sense for your team.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• ParentFolderId supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Folders WHERE Id = 50
SELECT * FROM Folders WHERE ParentFolderId = 2

Columns¶

FormHandlers¶

Retrieve,Create,Updata,Delete a collection of Form Handlers

Columns¶

FormHandlersFields¶

Retrieve,Create,Update,Delete a collection of Form Handler Stages

Columns¶

Forms¶

Retrieve forms.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM Forms WHERE Id <= 17
SELECT * FROM Forms WHERE IsDeleted = 'true'

GetDeleted¶

To retrieve forms that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Forms

Columns¶

LandingPages¶

Retrieving a collection of landing pages

Columns¶

LayoutTemplates¶

Retrieve and modify layout templates.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM LayoutTemplates WHERE Id = 40
SELECT * FROM LayoutTemplates WHERE IsDeleted = 'true'

Insert¶

The following attributes are required when performing an insert: Name, LayoutContent.

INSERT INTO LayoutTemplates (Name, IsIncludeDefaultCss, LayoutContent, FolderId) VALUES ('name', 'true', 'content', 33621)

Update¶

Updating LayoutTemplates:

UPDATE LayoutTemplates SET Name = 'newName' WHERE ID = 24143

Delete

Remove all layout templates or a layout template by specifying the ID of the template.

DELETE FROM LayoutTemplates WHERE ID = 10003

GetDeleted¶

To retrieve layout templates that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM LayoutTemplates

Columns¶

LifecycleStages¶

Retrieving a collection of lifecycle stages

Columns¶

ListEmail¶

A list email is a single email sent to a recipient list.

Table Specific Information¶

Select¶

The connector uses the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.
• SentAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM ListEmail WHERE Id = 50
SELECT * FROM ListEmail WHERE SentAt > '01/01/2022'
SELECT * FROM ListEmail WHERE CreatedAt > '01/01/2022'

GetDeleted¶

To retrieve list email that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM ListEmail

Columns¶

ListMemberships¶

Retrieve,Create,Update,Delete a collection of list memberships

Columns¶

Lists¶

Retrieve and modify lists.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM Lists WHERE Id = 40
SELECT * FROM Lists WHERE IsDeleted = 'true'
SELECT * FROM Lists WHERE CreatedAt > '01/01/2022'

Insert¶

The following attribute is required when performing an insert: Name.

INSERT INTO Lists (Name) VALUES ('name')

Update¶

Update Lists attributes.

UPDATE Lists SET Name = 'newName' WHERE ID = 24143

Delete

Remove all lists or a list by specifying the ID of the list.

DELETE FROM Lists WHERE ID = 10003

GetDeleted¶

To retrieve lists that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Lists

Columns¶

Prospects¶

Retrieve and modify prospects.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• UserId supports '=', '<', '<=', '>', '>='.
• AssignedToId supports '=', '<', '<=', '>', '>='.
• LastActivityAt supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Prospects WHERE Id = 3457992
SELECT * FROM Prospects WHERE UserId = 14
SELECT * FROM Prospects WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'

Get Deleted¶

To retrieve deleted prospects, a GETDELETED statement can be executed.

GETDELETED FROM Prospects

Columns¶

TrackerDomains¶

Query the available tracker domains.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM TrackerDomains WHERE ID = 50

GetDeleted¶

To retrieve deleted records, a GETDELETED statement can be executed.

GETDELETED FROM TrackerDomains

Columns¶

Users¶

Query the available users.

Table Specific Information¶

Select¶

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Users WHERE Id = 32
SELECT * FROM Users WHERE CreatedAt < '02/03/2019 23:11:33' AND CreatedAt > '02/03/2018 23:11:33'

Get Deleted¶

To retrieve deleted users, a GETDELETED statement can be executed.

GETDELETED FROM Users

Columns¶