Salesforce Pardot Connection Details

Introduction

Connector Version

This documentation is based on version 21.0.8662 of the connector.

Get Started

Salesforce Pardot Version Support

The connector leverages the SalesforcePardot API to enable bidirectional access to SalesforcePardot. Supports Pardot API v3 and v4.

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.

Use OAuth Authentication

OAuth requires the use of access tokens obtained with Postman or a similar tool. OAuth authentication is supported as described in Connection under Configure OAuth Connections.

Embedded Credentials

Authenticate using the Embedded OAuth Credentials

Desktop Authentication with the Embedded OAuth App

You can connect without setting any connection properties for your user credentials. After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• BusinessUnitID: Set this to your business unit id.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation so the the values persist across connections.

Custom Credentials

This section describes desktop authentication using the credentials for your custom OAuth app. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

After setting the following, you are ready to connect:

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

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the following OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Authenticate with OAuthJWT Certificate Authentication

Set the AuthScheme to OAuthJWT.

After creating your OAuth Application, set the following connection properties:

• AuthScheme: Set to OAuthJWT.
• InitiateOAuth: Set to GETANDREFRESH.
• OAuthJWTCert: Set this to the JWT Certificate store.
• OAuthJWTPassword: Set this to the Password of the JWT Certificate store.
• OAuthJWTIssuer: Set this to the OAuth Client ID.
• OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Headless Machines

To create Salesforce Pardot data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the connector to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the connector's embedded OAuth credentials or using the OAuth credentials for your custom OAuth app.

Use the Credentials for a Custom OAuth App

Create a Custom OAuth App

See Creating a Custom OAuth App for a procedure. This section describes the procedure to authenticate and connect to data.

Obtain a Verifier Code

First, set the following properties on the headless machine:

• InitiateOAuth: Set to OFF.
• OAuthClientId: Set to the App ID in your app settings.
• OAuthClientSecret: Set to the App Secret in your app settings.

Next, authenticate from another machine and obtain the OAuthVerifier connection property:

1. Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You need to set this in the OAuthVerifier connection property.

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

• OAuthClientId: Set to the consumer key in your app settings.
• OAuthClientSecret: Set to the consumer secret in your app settings.
• OAuthVerifier: Set to the verifier code.
• OAuthSettingsLocation: Set to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set to REFRESH.

Connect to Data

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthSettingsLocation: Set to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set to REFRESH.

Transfer OAuth Settings

To install the connector on another machine, authenticate, and then transfer the resulting OAuth values:

1. On a second machine, install the connector and connect with the following properties set:
   • OAuthSettingsLocation: Set to a writable text file.
   • InitiateOAuth: Set to GETANDREFRESH.
   • OAuthClientId: Set to the Client ID in your app settings.
   • OAuthClientSecret: Set to the Client Secret in your app settings.
   • CallbackURL: Set to the Callback URL in your app settings.
2. Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
   • InitiateOAuth: Set to REFRESH.
   • OAuthSettingsLocation: Set to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Authenticate with OAuthJWT Certificate Authentication

Set the AuthScheme to OAuthJWT.

After creating your OAuth Application, set the following connection properties:

• AuthScheme: Set to OAuthJWT.
• InitiateOAuth: Set to GETANDREFRESH.
• OAuthJWTCert: Set this to the JWT Certificate store.
• OAuthJWTPassword: Set this to the Password of the JWT Certificate store.
• OAuthJWTIssuer: Set this to the OAuth Client ID.
• OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Create a Custom OAuth App

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 Pardot uses the OAuth authentication standard, which requires the authenticating user to interact with Salesforce Pardot via the browser. The connector facilitates the OAuth exchange in various ways, as described in this section.

Create a Connected App

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 app. 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 app, along with a contact Email address.
4. Click Enable OAuth Settings and enter a value in the Callback URL box. If you are making a desktop application, set the Callback URL to http://localhost:33333 or a different port number of your choice. If you are making a web application, set the Callback URL to a page on your Web app you want the user to be returned to after they have authorized your application.
5. Select the scope of permissions that your app should request from the user. At least the "pardot_api" and "api" scopes and/or "full" scope must be among the selected OAuth scopes. Otherwise, only the username-password OAuth flow can be used with the Pardot API.
6. In addition, the "refresh_token" scope must be selected. By adding this scope, the API will return a refresh token in the OAuth request, which will be used by the driver to automatically refresh the access token when it expires.
7. Click your app name to open a page with information about your app. The OAuth client credentials, the consumer key, and consumer secret are 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 Pardot connector.

User Defined Views

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

SSL Configuration

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

Proxy

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

User Defined Views

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

{
    "MyView": {
        "query": "SELECT * FROM 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"

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 Pardot APIs.

Key Features

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

Collaborative Query Processing

The connector offloads as much of the SELECT statement processing as possible to Salesforce Pardot and then processes the rest of the query in memory. API limitations and requirements are also documented in this section.

See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.

Salesforce Pardot V3 & V4 Data Model

The Salesforce Pardot connector models Salesforce Pardot 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 Pardot into a list of tables that can be queried using standard SQL statements.

Generally, querying Salesforce Pardot tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Salesforce Pardot Connector Tables

Campaigns

Retrieve and modify campaigns.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 Pardot 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 Pardot 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 Pardot 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 composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Salesforce Pardot Connector 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 Pardot 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 available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Salesforce Pardot Connector 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 Salesforce Pardot connector models Salesforce Pardot 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 Pardot into a list of tables that can be queried using standard SQL statements.

Generally, querying Salesforce Pardot tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Salesforce Pardot Connector Tables

CustomRedirects

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

Table Specific Information

Select

The connector will use the Salesforce Pardot 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

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

EmailTemplates

Query email templates.

Table Specific Information

Select

The connector uses the Salesforce Pardot 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'

To insert into a 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. To submit JSON data, supply the values for the child table in JSON format. In order to execute the insert, the complete JSON can be referenced or included as string literals. For example:

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 Pardot 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

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.

LayoutTemplates

Retrieve and modify layout templates.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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

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

Lists

Retrieve and modify lists.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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

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 Pardot 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

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Salesforce Pardot Connector Views

Campaigns

Retrieve and modify campaigns.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 Pardot 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.

Emails

Query emails to learn how prospects responded to the emails.

Table Specific Information

Select

The connector uses the Salesforce Pardot 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

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 Pardot 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

Forms

Retrieve forms.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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

ListEmail

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

Table Specific Information

Select

The connector uses the Salesforce Pardot 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

ProspectAccounts

Retrieve and modify prospect accounts.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM ProspectAccounts WHERE ID > 31

Get Deleted

To retrieve deleted prospect accounts, a GETDELETED statement can be executed.

GETDELETED FROM ProspectAccounts

Columns

ReplyToOptions

Query the repltyTo options of email templates.

Columns

SenderOptions

Query the sender options of email templates.

Columns

TrackerDomains

Query the available tracker domains.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 Pardot 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

VisitorsPageViews

Retrieve visitors page views.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 '=', '<', '<=', '>', '>='.
• VisitId supports '=', '<', '<=', '>', '>='.
• VisitorId supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM VisitorPageViews WHERE VisitId = 22
SELECT * FROM VisitorPageViews WHERE ID >= 22 AND VisitorId = 1234

Columns

Visits

Retrieve visits.

Table Specific Information

Select

The connector will use the Salesforce Pardot 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 '=', '<', '<=', '>', '>='.
• VisitorId supports '=', '<', '<=', '>', '>='.
• ProspectId supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Visits WHERE Id = 22
SELECT * FROM Visits WHERE VisitorId > 234

Columns

Stored Procedures

Stored procedures are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Salesforce Pardot Connector Stored Procedures

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