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 Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Salesforce Marketing Cloud 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