Skip to Content

Microsoft Exchange v2 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8858 of the connector.

Get Started

Microsoft Exchange Version Support

The Jitterbit Connector for Microsoft Exchange connects with the REST API.

Establish a Connection

Connect to Microsoft Exchange

There are two schemas available for connecting to Exchange:

  • Exchange Web Services (EWS), which is no longer being updated but is still available for both Exchange OnPremise and Exchange Online.

Note

Microsoft recommends that Exchange Online users switch to Microsoft Graph.

  • Microsoft Graph

For a look at the data model for each of these schemas, see "Data Model".

To switch between EWS and Microsoft Graph, set Schema to either EWS or MSGraph.

Exchange Online users who still want to use EWS should set Schema to EWS and the Platform to Exchange_Online.

Exchange OnPremise

Microsoft Exchange OnPremise defaults set User, Password, and AuthScheme to support Basic authentication. However, OnPremise also supports authentication via Windows (NTLM), Kerberos (described below) and deletation.

Exchange Online

Exchange Online supports authentication via OAuth, as described below. (Azure AD and Azure Service Principal also use custom OAuth applications for authentication.)

If you are connecting to Exchange Online platform through EWS, set AuthScheme to AzureAD, AzureServicePrincipal, or AzureMSI.

If you connect to Exchange Online through Microsoft Graph, set Schema to MSGraph. When Schema is set to MSGraph, the Platform is ignored.

Authenticate to Microsoft Exchange

Microsoft Exchange supports authentication using OAuth (Azure AD and Azure Service Principal), Managed Service Identity (MSI), or Kerberos.

OAuth

Azure AD and Azure Service Principal both require OAuth-based authentication.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

Microsoft Exchange provides embedded OAuth credentials that simplify connection from a Desktop application . To connect from a Web application, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

To connect via OAuth from all authentication flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Microsoft Exchange from all applicable auth flows. For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that already have embedded OAuth credentials, see Creating a Custom OAuth Application. For a complete list of connection string properties available in Microsoft Exchange, see Connection.

Desktop Applications

provides an embedded OAuth application that simplifies authentication at the desktop.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Microsoft Exchange console. For further information, see Creating a Custom OAuth Application.

Before you connect, set the following variables:

  • InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken. provides an embedded OAuth application that simplifies authentication at the desktop; that is, in situations where the user is using a local server not connected to the internet.

    You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Microsoft Exchange console. For further information, see Creating a Custom OAuth Application.

  • Custom Azure AD applications only:

    • OAuthClientId: The client ID assigned when you registered your custom OAuth application.
    • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
    • CallbackURL: The redirect URI defined when you registered your custom OAuth application.

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

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

When the access token expires, the connector refreshes it automatically.

Azure Service Principal

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken by the application are executed without a default user context, but based on the assigned roles.

The application access to the resources is controlled through the assigned roles' permissions.

For information about how to set up Azure Service Principal authentication, see Creating a Custom OAuth Application.

Authenticate via Client Secret

OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

All tasks accepted by the application are executed outside of the context of a default user.

Note

Since the embedded OAuth credentials authenticate on a per-user basis, you cannot

use them in a client OAuth flow. You must always create a custom OAuth application to use client credentials.

Authentication via client credentials requires the use of a custom OAuth application. For further information, see Creating a Custom OAuth Application.

After you set these parameters, you are ready to connect.

  • AuthScheme: AzureServicePrincipal.
  • InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • AzureTenant: The tenant you wish to connect to.
  • OAuthGrantType: CLIENT.
  • OAuthClientId: The client ID in your application settings.
  • OAuthClientSecret: The client secret in your application settings.

Authentication with client credentials takes place automatically, just like any other connection, except that no window opens to prompt the user to log in. Because there is no user context, there is no need for a browser popup. Connections are made and handled internally.

Managed Service Identity (MSI)

If you are running Microsoft Exchange on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Kerberos

Authenticating to Microsoft Exchange via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

To authenticate to Microsoft Exchange using Kerberos, set these properties:

  • AuthScheme: NEGOTIATE.
  • KerberosKDC: The host name or IP Address of your Kerberos KDC machine.
  • KerberosRealm: The realm of the Microsoft Exchange Kerberos principal. Find this value immediately after the '@' symbol of the principal value.
  • KerberosSPN: The service and host of the Microsoft Exchange Kerberos Principal. Find this value just before the '@' symbol of the principal value.

For further information on how to use Kerberos in an Microsoft Exchange environment, see Using Kerberos.

Use Azure Service Principal

Azure Service Principal

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

To use Azure Service Principal authentication, you must:

  • Set up the ability to assign a role to the authentication application. To do this, create a custom OAuth AD application, as described in Creating a Custom OAuth Application.
  • Register an application with an Azure AD tenant, to create a new service principal that can be used with the role-based access control, to access resources in your subscription.

Do the following:

  1. Create a custom Azure AD application, as described in Creating a Custom OAuth Application.
  2. Assign a role to the application:
    1. Use the search bar to search for the Subscriptions service.
    2. Open the Subscriptions page.
    3. Select the subscription to which to assign the application.
    4. Open the Access control (IAM).
    5. Select Add > Add role assignment. Microsoft Exchange opens the Add role assignment page.
    6. Assign your custom Azure AD application the role of Owner.

Admin consent occurs when the Admin for an Azure Active Directory tenant grants permissions to a custom application that explicitly requires an admin to consent to the use case.

When creating a new Azure AD application in the Azure Portal, you must specify which permissions the application requires. Some permissions may be marked as "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your application requires admin consent, there are two ways you can do this.

The easiest way to grant admin consent is to have an admin log into portal.azure.com and navigate to the application you have created in App Registrations. Under API Permissions, click Grant Consent. This gives your application permissions on the tenant under which it was created.

If your organization has multiple tenants or you must grant application permissions for other tenants outside your organization, use the GetAdminConsentURL stored procedure to generate the Admin Authorization URL. Unlike the GetOAuthAuthorizationURL stored procedure, no important information is returned from this endpoint. Rather, after the OAuth application is successfully authorized, it returns a Boolean indicating that permissions have been granted.

After the administrator has approved the OAuth Application, you can continue to authenticate.

Client Credentials

Client credentials refers to a flow in OAuth where there is no direct user authentication taking place. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context. This makes the authentication flow a bit different from the standard flow.

All permissions related to the client OAuth flow require admin consent. This means you cannot use the application embedded with the Jitterbit Connector for Microsoft Exchange in the client OAuth flow. You must create your own OAuth application to use client credentials. See Creating a Custom OAuth Application for more information.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under "Application Permissions".

Select the permissions you require for your integration. After you do this, set the following connection properties:

  • AuthScheme: AzureServicePrincipal.
  • InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • AzureTenant: The tenant you wish to connect to.
  • OAuthGrantType: CLIENT.
  • OAuthClientId: The client ID in your application settings.
  • OAuthClientSecret: The client secret in your application settings.

Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections will take place and be handled internally.

Create a Custom OAuth Application

Create a Custom OAuth Application

Microsoft Exchange supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

This topic describes how to:

  • create and register custom OAuth application for Azure AD or Azure Service Principal
  • provide Admin Consent to a custom OAuth application
  • create a custom OAuth application for use with client credentials
Azure AD

In portal.azure.com:

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
  3. Click New registration.
  4. Enter a name for the application.
  5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
    • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Jitterbit Connector for Microsoft Exchange. Otherwise, the authentication attempt fails with an error.
    • If your application is for private use only, specify Accounts in this organization directory only.
    • If you want to distribute your application, choose one of the multi-tenant options.
  6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
  7. Click Register to register the new application. An application management screen displays.
    Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
  8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
    • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
    • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft Exchange displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
  9. Select API Permissions > Add > Delegated permissions.
  10. If you are connecting to Microsoft Exchange through EWS schema, select Exchange API and add EWS.AccessAsUser.All permission. If you are connecting to Microsoft Exchange through MSGraph schema, select Microsoft Graph API and add the following permissions: Calendars.ReadWrite.Shared, Contacts.ReadWrite, Group.Read.All, Group.ReadWrite.All, User.ReadWrite.All, and Mail.ReadWrite.Shared.
  11. Save your changes.
  12. If you have specified the use of permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.
Azure Service Principal

To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

control to access resources in your subscription.

In portal.azure.com:

  1. Create a custom OAuth AD application, as described above.
  2. Use the search bar to search for the Subscriptions service.
  3. Open the Subscriptions page.
  4. Select the subscription to which to assign the application.
  5. Open the Access control (IAM).
  6. Select Add > Add role assignment. Microsoft Exchange opens the Add role assignment page.
  7. Assign your custom Azure AD application the role of Owner.

Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

To grant admin consent:

  1. Have an admin log in to portal.azure.com.
  2. Navigate to App Registrations and find the custom OAuth application you created.
  3. Under API Permissions, click Grant Consent.

This gives your application permissions on the tenant under which it was created.

OAuth supports the use of client credentials to authenticate. In a client credentials OAuth flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow except that there is no prompt for an associated user to provide credentials.

All tasks accepted by the application are executed outside of the context of a default user.

Note

Since the embedded OAuth credentials authenticate on a per-user basis, you cannot

use them in a client OAuth flow. You must always create a custom OAuth application to use client credentials.

In portal.azure.com:

  1. Create a custom OAuth application, as described above.
  2. Navigate to App Registrations.
  3. Find the application you just created, and open API Permissions.
  4. Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
  5. Under Application Permissions, select the permissions you require for your integration.

Use Kerberos

Kerberos

To authenticate to Microsoft Exchange with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to Microsoft Exchange via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log

in at their local computer or enter kinit USER at the command prompt.

The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Cerberos Credential Cache Files:

  1. Ensure that the KRB5CCNAME variable is present in your environment.
  2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
  3. To obtain a ticket:

    1. Open the MIT Kerberos Ticket Manager application.
    2. Click Get Ticket.
    3. Enter your principal name and password.
    4. Click OK.

    If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The connector uses the cache file to obtain the Kerberos ticket to connect to Microsoft Exchange.

Note

If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the connector uses the specified cache file to obtain the Kerberos ticket to connect to Microsoft Exchange.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with Microsoft Exchange.

Enable Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Exchange Online Administrative Tasks

The Jitterbit Connector for Microsoft Exchange can be used to perform administrative tasks with MSGraph Schema. This can be done by specifying the UserId column to execute CUD operations.

The UserId Column

Many tables expose a special UserId column. This is designed to be used by an administrator to modify records on another user's account. If you are not an administrator or do not desire this behavior, do not specify the UserId when performing an INSERT / UPDATE / DELETE operation. For instance, executing the following will insert a contact for another user:

INSERT INTO Contacts (displayName, CompanyName, UserId) VALUES ('Bill', 'Bob Co', '12345')

The above request will have the overall effect of attempting to add a contact under the resource at /users/12345/contacts. When UserId is not specified, the resources affected will instead be modified under /users/me/contacts. In general if you are not an administrator, you can only affect or view records under /users/me, so it is not recommended to set UserId when you are not an admin.

Note

Specifying UserId in conjunction with an 'OR' operator of any given query is not supported. As an alternative you can use 'UNION' to retrieve the same result set.

Select Exchange Data

Note: The following describes the behavior when Schema is set to EWS. It has no impact on MSGraph.

FindItem vs. GetItem

By default, the connector will perform the Exchange Web Services FindItem API call and only request summary information about items when a SELECT operation is performed. Any request that could return more than one item will only return summary information. For example:

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE Surname='Smith'

If you wish to request the contents of a message or more information about a contact or calendar event, you will need to set IncludeContent to TRUE, specify the ItemIds of the items, or limit your results to a single item. For example:

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE ItemId='AZQ111222...'

OR

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE ItemdId IN ('AZQ111222...', 'AZQ111223...', 'AZQ111224...', 'AZQ111225...')

OR

SELECT ItemId, Surname, EmailAddress1 FROM Contacts WHERE Surname='Smith' LIMIT 1

Public and Custom Folders

If you wish to read from a Public or Custom folder, you'll need to first identify the FolderId of the folder you wish to read from. This can be done by submitting a read query from the ParentFolder (for a custom folder) or from the relevant table for the type of object stored in a Public Folder, using Inbox if the Public Folder

contains messages. For example:

Finding the FolderId of a subfolder of the Inbox:

SELECT DisplayName, FolderId FROM Inbox

Finding the FolderId of a Custom Folder that contains Contacts:

SELECT DisplayName, FolderId FROM Contacts WHERE ParentFolderName='publicfoldersroot'

If your public folder is nested, you may need to do a separate SELECT query on the parent custom folder:

SELECT DisplayName, FolderId FROM Contacts WHERE ParentFolderId='AAEuAAAAAAAa...'

Create Items in Public and Custom Folders

Insert into Public and Custom Folders

If you wish to create an item in a Public or Custom folder, you'll need to first identify the FolderID of the folder you wish to read from (see Selecting Exchange Data). Once you know the FolderID, you can use this value as the ParentFolderId when you create a new object. To create an object in a custom or public folder, use the relevant

object type as the table (or Inbox if the folder contains Messages). For example:

Inserting into a subfolder of the Inbox:

INSERT INTO Inbox (Subject, FromEmailAddress, ToRecipients_EmailAddress, ParentFolderId) VALUES ('New email message', 'user1@email.com', 'user2@email.com', 'AAEuAAAAAAAa...')

Inserting into a Public Folder that contains Contacts:

INSERT INTO Contacts (GivenName, Surname, EmailAddress1, ParentFolderId) VALUES ('Jill', 'Smith', 'user3@email.com', 'AAEuAAAAAAAa...')

Update or Deleting Items in Public and Custom Folders

Update or Deleting an Item in a Public and Custom Folders

Unlike reading from or inserting into a Public or Custom folder, you do not need the FolderId in order to update or delete an item. You simply need to know what type

of object the folder contains and use that type as the Table (or use Inbox for a folder that contains Messages). For example:

Updating a Message item in a custom folder:

UPDATE Inbox SET Subject = 'Updated email message' WHERE ItemID = 'AZQ111222...')

Deleting a Contact item from a Public Folder:

DELETE FROM Contacts WHERE ItemID = 'AZQ111222...')

Fine-Tuning Data Access

Impersonate Users

This authentication method is typically used by administrators to configure access by a service account.
To impersonate all requests, set the following connection properties at connection time.
To impersonate a user for an individual query, use the pseudo columns of the same name.

  • ImpersonationUser: Set this to the user to impersonate.
  • ImpersonationType: Set this to the format you are using to specify the user. For example, UPN or SID.

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 Microsoft Exchange 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 Microsoft Exchange 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 Microsoft Exchange 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 Contacts WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

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

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

Define Views Using DDL Statements

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

Create a View

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

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

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

Alter a View

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

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

The view is then updated in the JSON configuration file.

Drop a View

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views

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

Work with User Defined Views

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration

Customize the SSL Configuration

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

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

Data Model

The Jitterbit Connector for Microsoft Exchange models the Microsoft Exchange 2 and Microsoft Exchange APIs as relational tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The available entities, as well as any API limitations and requirements for querying these entities, are documented in EWS Data Model and MSGraph Data Model.

Overview

The Data Models illustrate an example of what your Microsoft Exchange environment might look like. The actual data model will be obtained dynamically based on your Microsoft Exchange account.

Key Features

  • Tables and Views are dynamically defined to model calendars, documents, and projects on Microsoft Exchange.
  • Stored procedures allow you to execute operations to Microsoft Exchange, including downloading and uploading objects.
  • Live connectivity to these objects means any changes to your Microsoft Exchange account are immediately reflected when using the connector.

EWS Data Model

EWS Data Model describes the schemas available to connect to Microsoft Exchange OnPremise and Microsoft Exchange Online using EWS. You can use tables to work with live Microsoft Exchange data. You can use stored procedures provided by Jitterbit Connector for Microsoft Exchange to automate working with Microsoft Exchange data.

MSGraph Data Model

MSGraph Data Model describes the schemas available to connect to Microsoft Exchange Online accounts via the Microsoft Graph. You can use tables to work with live Microsoft Exchange data. You can use stored procedures provided by Jitterbit Connector for Microsoft Exchange to automate working with Microsoft Exchange data.

EWS Data Model

The Jitterbit Connector for Microsoft Exchange models Microsoft Exchange entities as relational Tables and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Stored Procedures are function-like interfaces to Microsoft Exchange. They can be used to search, update, and modify information in Microsoft Exchange.

Tables

The connector models the data in Microsoft Exchange as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Microsoft Exchange Tables
Name Description
Calendar Create, update, delete, and query Calendar items.
Calendar_OptionalAttendees The optional attendees for a particular event. An ItemId must be specified when querying this view.
Calendar_RequiredAttendees The required attendees for a particular event. An ItemId must be specified when querying this view.
Contacts Create, update, delete, and query Contacts items.
DeletedItems Create, update, delete, and query Deleted Items.
Drafts Create, update, delete, and query Drafts items.
Folders Create, update, delete, and query subfolders for a given folder.
Inbox Create, update, delete, and query Inbox items.
JunkEmail Create, update, delete, and query Junk Email items.
Outbox Create, update, delete, and query Outbox items.
SentItems Create, update, delete, and query Sent Items.
Tasks Create, update, delete, and query Tasks items.
Calendar

Create, update, delete, and query Calendar items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

SELECT Recurring Events

When performing a SELECT operation on the Calendar table, the connector will not include individual recurring events by default (only the master item will be included). Your query will need to include a WHERE clause similar to the following:

SELECT Subject, IsRecurring, Recurrence_StartDate, Recurrence_EndDate, Recurrence_Interval, Recurrence_Type, Recurrence_NumberOfOccurrences, FirstOccurrence_Start FROM Calendar WHERE ItemId = 'myid'

If you wish to view the individual recurrences of a recurring event, you'll need to add filters START and END with greaterthan and lessthan operators respectively. The interval between START and END should not be more than 2 years. The API will return atmost 1000 entries for recurring events. Please give the interval between START and END filters accordingly. Your query will need to include a WHERE clause similar to the following:

SELECT * FROM Calendar WHERE start >= '2021-05-11 00:50:59.0' AND end <= '2023-02-28 11:20:30.0'
Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store. This property is read-only.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store. This property is read-only.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder. This property is read-only.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder. This property is read-only.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Body of the Calendar Item.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String True Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
Start Datetime False Represents the start of a duration.
End Datetime False Represents the end of a duration.
OriginalStart Datetime False Represents the original start time of a calendar item.
IsAllDayEvent Boolean False Indicates whether a calendar item or meeting request represents an all-day event.
LegacyFreeBusyStatus String False Represents the free/busy status of the calendar item.
Location String False Represents the location of a meeting, appointment, or persona.
When String False Provides information about when a calendar or meeting item occurs.
IsMeeting Boolean False Indicates whether the calendar item is a meeting or an appointment.
IsCancelled Boolean False Indicates whether an appointment or meeting has been cancelled.
IsRecurring Boolean True Indicates whether a calendar item, meeting request, or task is part of a recurring item. This element is read-only.
MeetingRequestWasSent Boolean True Indicates whether a meeting request has been sent to requested attendees.
IsResponseRequested Boolean True Indicates whether a response to an item is requested.
CalendarItemType String True Represents the type of a calendar item.
MyResponseType String False Contains the status of or response to a calendar item.
OrganizerName String False Defines the name of the mailbox user.
OrganizerEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Organizer mailbox user.
OrganizerRoutingType String False Defines the routing that is used for the Organizer mailbox. The default is SMTP.
ConflictingMeetingCount Integer True Represents the number of meetings that conflict with the calendar item.
AdjacentMeetingCount Integer True Represents the total number of calendar items that are adjacent to a meeting time.
Duration String True Represents the duration of a calendar item.
TimeZone String True Provides a text description of a time zone.
AppointmentReplyTime Datetime False Represents the date and time that an attendee replied to a meeting request.
AppointmentSequenceNumber String False Specifies the sequence number of a version of an appointment.
AppointmentState String True Specifies the status of the appointment.
Recurrence_Type String False Contains the recurrence type for calendar items and meeting requests. The allowed values are RelativeYearlyRecurrence, AbsoluteYearlyRecurrence, RelativeMonthlyRecurrence, AbsoluteMonthlyRecurrence, WeeklyRecurrence, DailyRecurrence.
Recurrence_DaysOfWeek String False Describes days of the week that are used in item recurrence patterns.
Recurrence_DayOfWeekIndex String False Describes which week in a month is used in a relative yearly recurrence pattern. The allowed values are First, Second, Third, Fourth, Last.
Recurrence_Month String False Describes the month when a yearly recurring item occurs.
Recurrence_DayOfMonth Integer False Describes the day in a month on which a recurring item occurs.
Recurrence_Interval Integer False Defines the interval between two consecutive recurring items.
Recurrence_FirstDayOfWeek String False Specifies the first day of the week.
Recurrence_Duration String False Contains the recurrence duration type for calendar items and meeting requests. The allowed values are NoEndRecurrence, EndDateRecurrence, NumberedRecurrence.
Recurrence_StartDate Date False Represents the start date of a recurring task or calendar item.
Recurrence_EndDate Date False Represents the end date of a recurring task or calendar item.
Recurrence_NumberOfOccurrences Integer False Contains the number of occurrences of a recurring item.
FirstOccurrence_ItemId String False Contains the unique identifier of the last occurrence of a recurring calendar item.
FirstOccurrence_Start Datetime False Represents the start time of the last occurrence of a recurring calendar item.
FirstOccurrence_End Datetime False Represents the end time of the last occurrence of a recurring calendar item.
FirstOccurrence_OriginalStart Datetime False Represents the original start time of the last occurrence of a recurring calendar item.
LastOccurrence_ItemId String False Contains the unique identifier of the last occurrence of a recurring calendar item.
LastOccurrence_Start Datetime False Represents the start time of the last occurrence of a recurring calendar item.
LastOccurrence_End Datetime False Represents the end time of the last occurrence of a recurring calendar item.
LastOccurrence_OriginalStart Datetime False Represents the original start time of the last occurrence of a recurring calendar item.
ModifiedOccurrences_Aggregate String True Modified occurrences of the calendar, as aggregate.
DeletedOccurrences_Aggregate String True Deleted occurrences as aggregate.
StartTimeZone_Id String True Represents the unique identifier of the time zone definition for the start time.
StartTimeZone_Name String True Represents the descriptive name of the time zone definition for the start time.
EndTimeZone_Id String True Represents the unique identifier of the time zone definition for the end time.
EndTimeZone_Name String True Represents the descriptive name of the time zone definition for the end time.
ConferenceType Integer False Describes the type of conferencing that is performed with a calendar item (0 = NetMeeting, 1 = NetShow, 2 = Chat.
AllowNewTimeProposal Boolean False Indicates whether a new meeting time can be proposed for a meeting by an attendee.
IsOnlineMeeting Boolean False Indicates whether the meeting is online.
MeetingWorkspaceUrl String False Contains the URL for the meeting workspace that is linked to by the calendar item.
NetShowUrl String False Specifies the URL for a Microsoft NetShow online meeting.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Contains the identifier of an item or conversation.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SendMeetingInvitations String The allowed values are SendToNone, SendOnlyToAll, and SendToAllAndSaveCopy. The default is SendToNone.
SendCancellationsMode String The allowed values are SendToNone, SendOnlyToAll, and SendToAllAndSaveCopy. The default is SendToNone.
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the calendar events in a shared mailbox.
Calendar_OptionalAttendees

The optional attendees for a particular event. An ItemId must be specified when querying this view.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Inserting

When performing a SELECT operation on the Calendar table, the connector will not include individual recurring events by default (only the master item will be included). If you wish to view the individual recurrences of a recurring event, you'll need to filter the search by the IsRecurring column and use Start and End to specify a time period. Your query will need to include a WHERE clause similar to the following:

INSERT INTO Calendar_OptionalAttendees (EmailAddress, ItemId, SendMeetingInvitations) VALUES ('johndoe@example.com', 'itemid', 'SendOnlyToChanged')
Columns
Name Type ReadOnly Description
EmailAddress [KEY] String False
ItemId [KEY] String False
Name String False
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SendMeetingInvitations String The allowed values are SendToNone, SendOnlyToAll, SendOnlyToChanged and SendToAllAndSaveCopy. The default is SendToNone.
Calendar_RequiredAttendees

The required attendees for a particular event. An ItemId must be specified when querying this view.

Table Specific Information

Insert Statements

When performing an INSERT operation, you will need to specify EmailAddress and ItemId. Additionally, there is a property called SendMeetingInvitations you can set to decide who is notified when you insert required attendees.

INSERT INTO Calendar_RequiredAttendees (EmailAddress, ItemId, SendMeetingInvitations) VALUES ('johndoe@example.com', 'itemid', 'SendOnlyToChanged')
Columns
Name Type ReadOnly Description
EmailAddress [KEY] String False
ItemId String False
Name String False
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SendMeetingInvitations String The allowed values are SendToNone, SendOnlyToAll, SendOnlyToChanged and SendToAllAndSaveCopy. The default is SendToNone.
Contacts

Create, update, delete, and query Contacts items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store. This property is read-only.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store. This property is read-only.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder. This property is read-only.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder. This property is read-only.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
InReplyTo String True Represents the identifier of the item to which this item is a reply.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the Cc box. This is the concatenated string of all Cc recipient display names.
DisplayTo String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
HasAttachments Boolean True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
Culture String False Represents the culture for a given item in a mailbox.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
FileAs String False Represents how a contact or distribution list is filed in the Contacts folder.
FileAsMapping String False Defines how to construct what is displayed for a contact.
DisplayName String False Defines the display name of a folder, contact, distribution list, delegate user, location, or rule.
GivenName String False Contains the given name of a contact.
Initials String False Represents the initials of a contact.
MiddleName String False Represents the middle name of a contact.
Nickname String False Represents the nickname of a contact.
CompleteName_Title String True Represents the title of a contact.
CompleteName_FirstName String True Represents the first name of a contact.
CompleteName_MiddleName String True Represents the middle name of a contact.
CompleteName_LastName String True Represents the last name of a contact.
CompleteName_Suffix String True Represents the suffix of a contact.
CompleteName_Initials String True Represents the initials of a contact.
CompleteName_FullName String True Represents the full name of a contact.
CompleteName_Nickname String True Represents the nickname of a contact.
CompleteName_YomiFirstName String True Represents the name used in Japan for the searchable or phonetic spelling of a Japanese first name.
CompleteName_YomiLastName String True Represents the name used in Japan for the searchable or phonetic spelling of a Japanese first name.
CompanyName String False Represents the company name that is associated with a contact.
EmailAddress1 String False Represents a single email address for a contact.
EmailAddress2 String False Represents a single email address for a contact.
EmailAddress3 String False Represents a single email address for a contact.
BusinessAddress_Street String False Represents the Business street address for a contact item.
BusinessAddress_City String False Represents the Business city name for a contact item.
BusinessAddress_State String False Represents the Business state of residence for a contact item.
BusinessAddress_Country String False Represents the Business country or region for a contact item.
BusinessAddress_PostalCode String False Represents the Business postal code for a contact item.
HomeAddress_Street String False Represents the Home street address for a contact item.
HomeAddress_City String False Represents the Home city name for a contact item.
HomeAddress_State String False Represents the Home state of residence for a contact item.
HomeAddress_Country String False Represents the Home country or region for a contact item.
HomeAddress_PostalCode String False Represents the Home postal code for a contact item.
OtherAddress_Street String False Represents the Other street address for a contact item.
OtherAddress_City String False Represents the Other city name for a contact item.
OtherAddress_State String False Represents the Other state of residence for a contact item.
OtherAddress_Country String False Represents the Other country or region for a contact item.
OtherAddress_PostalCode String False Represents the Other postal code for a contact item.
AssistantPhone String False Represents the AssistantPhone number for a contact item.
BusinessFax String False Represents the BusinessFax number for a contact item.
BusinessPhone String False Represents the BusinessPhone number for a contact item.
BusinessPhone2 String False Represents the BusinessPhone2 number for a contact item.
Callback String False Represents the Callback number for a contact item.
CarPhone String False Represents the CarPhone number for a contact item.
CompanyMainPhone String False Represents the CompanyMainPhone number for a contact item.
HomeFax String False Represents the HomeFax number for a contact item.
HomePhone String False Represents the HomePhone number for a contact item.
HomePhone2 String False Represents the HomePhone2 number for a contact item.
Isdn String False Represents the Isdn number for a contact item.
MobilePhone String False Represents the MobilePhone number for a contact item.
OtherFax String False Represents the OtherFax number for a contact item.
OtherTelephone String False Represents the OtherTelephone number for a contact item.
Pager String False Represents the Pager number for a contact item.
PrimaryPhone String False Represents the PrimaryPhone number for a contact item.
RadioPhone String False Represents the RadioPhone number for a contact item.
Telex String False Represents the Telex number for a contact item.
TtyTddPhone String False Represents the TtyTddPhone number for a contact item.
AssistantName String False Represents an assistant to a contact.
Birthday Datetime False Represents the birth date of a contact.
BusinessHomePage String False Represents the Home page (Web address) for the contact.
Children_1 String False Contains the name of the child of a contact.
Children_2 String False Contains the name of the child of a contact.
Children_3 String False Contains the name of the child of a contact.
Companies_1 String False Represents the name of a company associated with a contact.
Companies_2 String False Represents the name of a company associated with a contact.
Companies_3 String False Represents the name of a company associated with a contact.
ContactSource String False Describes whether the contact is located in the Exchange store or Active Directory Domain Services.
Department String False Represents the department of a contact.
Generation String False Represents a generational abbreviation that follows the full name of a contact.
ImAddress1 String False Represents an instant messaging (IM) address for a contact.
ImAddress2 String False Represents an instant messaging (IM) address for a contact.
ImAddress3 String False Represents an instant messaging (IM) address for a contact.
JobTitle String False Represents the job title of a contact.
Manager String False Represents the manager of a contact.
Mileage String False Represents the mileage for a contact item.
OfficeLocation String False Represents the office location of a contact.
PostalAddressIndex String False Represents the display type for the physical address of a contact.
Profession String False Represents the profession of a contact.
SpouseName String False Represents the name of the spouse or partner of the contact.
Surname String False Represents the surname of a contact.
WeddingAnniversary Datetime False Contains the wedding anniversary of a contact.
HasPicture Boolean False Indicates whether the contact item has a file attachment that represents the picture of a contact.
PhoneticFullName String False Contains the full name of a contact, including the first and last name, spelled phonetically.
PhoneticFirstName String False Contains the first name of a contact, spelled phonetically.
PhoneticLastName String False Contains the last name of a contact, spelled phonetically.
Alias String False Contains the email alias of a contact.
Notes String False Contains supplementary contact information.
Photo String False Contains a value that encodes the photo of a contact.
UserSMIMECertificate String False Contains a value that encodes the SMIME certificate of a contact.
MSExchangeCertificate String False Contains a value that encodes the Microsoft Exchange certificate of a contact.
DirectoryId String False Contains the directory ID of a contact.
ManagerMailbox String False Contains SMTP information that identifies the mailbox of the manager of a contact.
DirectReports String False Contains SMTP information that identifies the direct reports of a contact.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the contacts of a shared mailbox.
DeletedItems

Create, update, delete, and query Deleted Items.

Table Specific Information

Select Emails from DeletedItems Subfolders

When performing a SELECT operation on the DeletedItems table, the connector will not include the items in the subfolders under DeletedItems, but only the items contained within the DeletedItems folder. If you wish to retrieve the items under the DeletedItems subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every DeletedItems subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the DeletedItems subfolders:

SELECT * FROM DeletedItems WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'DeletedItems' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentIds String True Contains a comma-separated list of the Ids of the attached files.
AttachmentNames String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Name String False Defines the name of the ToRecipient.
ToRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemId String False Defines the item identifier of a ToRecipient.
CcRecipients_Name String False Defines the name of the CcRecipient.
CcRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemId String False Defines the item identifier of a CcRecipient.
BccRecipients_Name String False Defines the name of the BccRecipient.
BccRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemId String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the Deleted items of a shared mailbox.
Drafts

Create, update, delete, and query Drafts items.

Table Specific Information

Select Emails from Drafts Subfolders

When performing a SELECT operation on the Drafts table, the connector will not include the items in the subfolders under Drafts, but only the items contained within the Drafts folder. If you wish to retrieve the items under the Drafts subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Drafts subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Drafts subfolders:

SELECT * FROM Drafts WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Drafts' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentIds String True Contains a comma-separated list of the Ids of the attached files.
AttachmentNames String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the Cc box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Name String False Defines the name of the ToRecipient.
ToRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemId String False Defines the item identifier of a ToRecipient.
CcRecipients_Name String False Defines the name of the CcRecipient.
CcRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemId String False Defines the item identifier of a CcRecipient.
BccRecipients_Name String False Defines the name of the BccRecipient.
BccRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemId String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the Draft items of a shared mailbox.
Folders

Create, update, delete, and query subfolders for a given folder.

Table Specific Information

Update and Delete

The connector will need the FolderChangeKey to update or delete an item. However, if you are unsure of the FolderChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it take to perform a query.

Columns
Name Type ReadOnly Description
FolderId [KEY] String True Contains the unique identifier of an folder in the Exchange store.
FolderChangeKey String True Contains the unique change key of an folder in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
FolderClass String False Represents the folder class for a given folder.
DisplayName String False Contains the display name of a folder.
TotalCount Integer True Represents the total count of items within a given folder.
ChildFolderCount Integer True Represents the number of child folders that are contained within a folder.
CanDelete Boolean True Indicates whether a managed folder can be deleted by a customer.
CanRenameOrMove Boolean True Indicates whether a given managed folder can be renamed or moved by the customer.
MustDisplayComment Boolean True Indicates whether the managed folder comment must be displayed.
HasQuota Boolean True Indicates whether the managed folder has a quota.
IsManagedFoldersRoot Boolean True Indicates whether the managed folder is the root for all managed folders.
ManagedFolderId String True Contains the folder ID of the managed folder.
Comment String True Contains the comment that is associated with a managed folder.
StorageQuota Integer True Describes the storage quota for the managed folder.
FolderSize Integer True Describes the total size of all the contents of a managed folder.
HomePage String True Specifies the URL that will be the default home page for the managed folder.
UnreadCount Integer True Represents the count of unread items within a given folder.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
SharingEffectiveRights String False Indicates the permissions that the user has for the contact data that is being shared.
ParentFolderName String False Represents the distinguished folder ID of the parent folder.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
Traversal String Defines how a search is performed. One of [Shallow, Deep, SoftDeleted]. Default values is Shallow
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the shared folders.
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Inbox

Create, update, delete, and query Inbox items.

Table Specific Information

Select Emails from Inbox Subfolders

When performing a SELECT operation on the Inbox table, the connector will not include the items in the subfolders under Inbox, but only the items contained within the Inbox folder. If you wish to retrieve the items under the Inbox subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Inbox subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Inbox subfolders:

SELECT * FROM Inbox WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Inbox' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentId# String True Contains a comma-separated list of the Ids of the attached files.
AttachmentName# String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Names String False Defines the name of the ToRecipient.
ToRecipients_EmailAddresses String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemIds String False Defines the item identifier of a ToRecipient.
CcRecipients_Names String False Defines the name of the CcRecipient.
CcRecipients_EmailAddresses String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemIds String False Defines the item identifier of a CcRecipient.
BccRecipients_Name String False Defines the name of the BccRecipient.
BccRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemId String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the inbox items of a shared mailbox.
JunkEmail

Create, update, delete, and query Junk Email items.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentIds String True Contains a comma-separated list of the Ids of the attached files.
AttachmentNames String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Name String False Defines the name of the ToRecipient.
ToRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemId String False Defines the item identifier of a ToRecipient.
CcRecipients_Name String False Defines the name of the CcRecipient.
CcRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemId String False Defines the item identifier of a CcRecipient.
BccRecipients_Name String False Defines the name of the BccRecipient.
BccRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemId String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the Junk Emails of a shared mailbox.
Outbox

Create, update, delete, and query Outbox items.

Table Specific Information

Select Emails from Outbox Subfolders

When performing a SELECT operation on the Outbox table, the connector will not include the items in the subfolders under Outbox, but only the items contained within the Outbox folder. If you wish to retrieve the items under the Outbox subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every Outbox subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the Outbox subfolders:

SELECT * FROM Outbox WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'Outbox' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentIds String True Contains a comma-separated list of the Ids of the attached files.
AttachmentNames String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Name String False Defines the name of the ToRecipient.
ToRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemId String False Defines the item identifier of a ToRecipient.
CcRecipients_Name String False Defines the name of the CcRecipient.
CcRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemId String False Defines the item identifier of a CcRecipient.
BccRecipients_Name String False Defines the name of the BccRecipient.
BccRecipients_EmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemId String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the outbox items of a shared mailbox.
SentItems

Create, update, delete, and query Sent Items.

Table Specific Information

Select Emails from SentItems Subfolders

When performing a SELECT operation on the SentItems table, the connector will not include the items in the subfolders under SentItems, but only the items contained within the SentItems folder. If you wish to retrieve the items under the SentItems subfolders, your will need to specify the ParentFolderId-s in the WHERE clause. You can get every SentItems subfolder ID by executing a filtered by ParentFolderName query to the Folders table. You can use the example query below, to retrieve the items within the SentItems subfolders:

SELECT * FROM SentItems WHERE ParentFolderId IN (SELECT FolderId FROM Folders WHERE ParentFolderName = 'SentItems' AND TotalCount > 0)

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False Represents the actual body content of a message.
AttachmentIds String True Contains a comma-separated list of the Ids of the attached files.
AttachmentNames String True Contains a comma-separated list of the names of the attached files.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
Importance String False Describes the importance of an item.
InReplyTo String False Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs. This is used by the ReminderMinutesBeforeStart element to determine when the reminder is displayed.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
SenderName String False Defines the name of the Sender mailbox user.
SenderEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the Sender mailbox user.
SenderRoutingType String False Defines the routing that is used for the Sender mailbox. The default is SMTP.
ToRecipients_Names String False Defines the name of the ToRecipient.
ToRecipients_EmailAddresses String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ToRecipient.
ToRecipients_ItemIds String False Defines the item identifier of a ToRecipient.
CcRecipients_Names String False Defines the name of the CcRecipient.
CcRecipients_EmailAddresses String False Defines the Simple Mail Transfer Protocol (SMTP) address of the CcRecipient.
CcRecipients_ItemIds String False Defines the item identifier of a CcRecipient.
BccRecipients_Names String False Defines the name of the BccRecipient.
BccRecipients_EmailAddresses String False Defines the Simple Mail Transfer Protocol (SMTP) address of the BccRecipient.
BccRecipients_ItemIds String False Defines the item identifier of a BccRecipient.
IsReadReceiptRequested Boolean False Indicates whether the sender of an item requests a read receipt.
IsDeliveryReceiptRequested Boolean False Indicates whether the sender of an item requests a delivery receipt.
ConversationIndex String False Contains a binary ID that represents the thread to which this message belongs.
ConversationTopic String False Represents the conversation topic.
FromName String False Defines the name of the From mailbox user.
FromEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the From mailbox user.
FromRoutingType String False Defines the routing that is used for the From mailbox. The default is SMTP.
InternetMessageId String False Represents the Internet message identifier of an item.
IsRead Boolean False Indicates whether a message has been read.
IsResponseRequested Boolean False Indicates whether a response to an item is requested.
References String False Represents the Usenet header that is used to associate replies with the original messages.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
ReceivedByName String False Defines the name of the ReceivedBy mailbox user.
ReceivedByEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedBy mailbox user.
ReceivedByRoutingType String False Defines the routing that is used for the ReceivedBy mailbox. The default is SMTP.
ReceivedRepresentingName String False Defines the name of the ReceivedRepresenting mailbox user.
ReceivedRepresentingEmailAddress String False Defines the Simple Mail Transfer Protocol (SMTP) address of the ReceivedRepresenting mailbox user.
ReceivedRepresentingRoutingType String False Defines the routing that is used for the ReceivedRepresenting mailbox. The default is SMTP.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified. This element is read-only.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Identifies a specific conversation in the Exchange store.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String True Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String True Describes how the unique item body is stored in the item (HTML or TEXT).
ReminderMessageData_Text String False Specifies the text of a reminder message.
ReminderMessageData_Location String False Represents the location of a meeting, appointment, or persona.
ReminderMessageData_StartTime Datetime False Specifies the starting time of the item that the reminder is for.
ReminderMessageData_EndTime Datetime False Specifies the ending time of the item that the reminder is for.
ReminderMessageData_AssociatedCalendarId String True Represents the calendar item that is associated with the ReminderMessageData.
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the sent items of a shared mailbox.
Tasks

Create, update, delete, and query Tasks items.

Table Specific Information

Update and Delete

The connector will need the ItemChangeKey to update or delete an item. However, if you are unsure of the ItemChangeKey, the connector is able to retrieve it from the Exchange server automatically. Note that this may increase the time it takes to perform a query.

Recurrence Fields

In order to INSERT, SELECT, or UPDATE the Recurrence fields in a Task, you'll need to make sure that you only set the fields associated with the Recurrence_Type and Recurrence_Duration fields. Please see the tables below:

Recurrence_Type Values & Associated Fields

Property Description
RelativeYearlyRecurrence Recurrence_DaysOfWeek, Recurrence_DaysOfWeekIndex, Recurrence_Month
AbsoluteYearlyRecurrence Recurrence_DayOfMonth, Recurrence_Month
RelativeMonthlyRecurrence Recurrence_Interval, Recurrence_DaysOfWeek, Recurrence_DaysOfWeekIndex
AbsoluteMonthlyRecurrence Recurrence_Interval, Recurrence_DayOfMonth
WeeklyRecurrence Recurrence_Interval, Recurrence_DaysOfWeek, Recurrence_FirstDayOfWeek
Daily Recurrence_Interval
\*Regeneration Recurrence_Interval

Recurrence_Duration & Associated Fields

Property Description
NoEndRecurrence Recurrence_StartDate
EndDateRecurrence Recurrence_StartDate, Recurrence_EndDate
NumberedRecurrence Recurrence_StartDate, Recurrence_NumberOfOccurrences
Columns
Name Type ReadOnly Description
ItemId [KEY] String True Contains the unique identifier of an item in the Exchange store. This property is read-only.
MimeContent String True Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format.
ItemChangeKey String True Contains the unique change key of an item in the Exchange store. This property is read-only.
ParentFolderId String False Represents the unique identifier of the parent folder that contains the item or folder. This property is read-only.
ParentFolderChangeKey String True Represents the unique change key of the parent folder that contains the item or folder. This property is read-only.
ItemClass String False Represents the message class of an item.
Subject String False Represents the subject for Exchange store items and response objects. The subject is limited to 255 characters.
Sensitivity String True Indicates the sensitivity level of an item.
Body String False This field contains the body for the Task.
DateTimeReceived Datetime True Represents the date and time that an item in a mailbox was received.
Size Integer True Represents the size in bytes of an item. This property is read-only.
Categories String False Represents a collection of strings that identify to which categories an item in the mailbox belongs.
InReplyTo String True Represents the identifier of the item to which this item is a reply.
IsSubmitted Boolean True Indicates whether an item has been submitted to the Outbox default folder.
IsDraft Boolean True Represents whether an item has not yet been sent.
IsFromMe Boolean True Indicates whether a user sent an item to himself or herself.
IsResend Boolean True Indicates whether the item had previously been sent.
IsUnmodified Boolean True Indicates whether the item has been modified.
InternetMessageHeaders String True Represents the collection of all Internet message headers that are contained within an item in a mailbox.
DateTimeSent Datetime True Represents the date and time that an item in a mailbox was sent.
DateTimeCreated Datetime True Represents the date and time that a given item in the mailbox was created.
ReminderDueBy Datetime False Represents the date and time when the event occurs.
ReminderIsSet Boolean False Indicates whether a reminder has been set for an item in the Exchange store.
ReminderMinutesBeforeStart Integer False Represents the number of minutes before an event occurs when a reminder is displayed.
DisplayCc String True Represents the display string that is used for the contents of the To box. This is the concatenated string of all To recipient display names.
DisplayTo String True Represents a property that is set to true if an item has at least one visible attachment. This property is read-only.
HasAttachments Boolean True Indicates whether an item has attachments.
Culture String False Represents the Culture of an item.
ActualWork Integer False Represents the actual amount of time that is spent on a task.
AssignedTime Datetime False Represents the time when a task is assigned to a contact.
BillingInformation String False Holds billing information for a task.
ChangeCount Integer False Specifies the version of the task.
Companies1 String False Represents the collection of companies that are associated with a contact or task.
Companies2 String False Represents the collection of companies that are associated with a contact or task.
Companies3 String False Represents the collection of companies that are associated with a contact or task.
CompleteDate Datetime False Represents the date on which a task is completed.
Contacts1 String False Contains a list of contacts who are associated with a task.
Contacts2 String False Contains a list of contacts who are associated with a task.
Contacts3 String False Contains a list of contacts who are associated with a task.
DelegationState String False Represents the status of a delegated task.
Delegator String False Contains the name of the delegator who assigned the task.
DueDate Datetime False Represents the date when a task item is due.
IsAssignmentEditable Boolean False Indicates whether the task is editable or not.
IsComplete Boolean False Indicates whether the task has been completed or not.
IsRecurring Boolean True Indicates whether a task is part of a recurring item.
IsTeamTask Boolean False Indicates whether the task is owned by a team or not.
Mileage String False Represents mileage for a task item.
Owner String False Represents the owner of a task.
PercentComplete Double False Describes the completion status of a task.
Recurrence_Type String False Contains the recurrence type for task items and meeting requests. The allowed values are RelativeYearlyRecurrence, AbsoluteYearlyRecurrence, RelativeMonthlyRecurrence, AbsoluteMonthlyRecurrence, WeeklyRecurrence, DailyRecurrence, DailyRegeneration, WeeklyRegeneration, MonthlyRegeneration, YearlyRegeneration.
Recurrence_DaysOfWeek String False Describes days of the week that are used in item recurrence patterns.
Recurrence_DayOfWeekIndex String False Describes which week in a month is used in a relative yearly recurrence pattern. The allowed values are First, Second, Third, Fourth, Last.
Recurrence_Month String False Describes the month when a yearly recurring item occurs.
Recurrence_DayOfMonth Integer False Describes the day in a month on which a recurring item occurs.
Recurrence_Interval Integer False Defines the interval between two consecutive recurring items.
Recurrence_FirstDayOfWeek String False Specifies the first day of the week.
Recurrence_Duration String False Contains the recurrence duration type for task items and meeting requests. The allowed values are NoEndRecurrence, EndDateRecurrence, NumberedRecurrence.
Recurrence_StartDate Datetime False Represents the start date of a recurring task or calendar item.
Recurrence_EndDate Datetime False Represents the end date of a recurring task or calendar item.
Recurrence_NumberOfOccurrences Integer False Contains the number of occurrences of a recurring item.
StartDate Datetime False Represents the start date of a task item.
Status String False Represents the status of a task item.
StatusDescription String True Contains an explanation of the task status.
TotalWork Integer False Contains a description of how much work is associated with an item.
EffectiveRights_CreateAssociated Boolean True Indicates whether a client can create an associated contents table.
EffectiveRights_CreateContents Boolean True Indicates whether a client can create a contents table.
EffectiveRights_CreateHierarchy Boolean True Indicates whether a client can create a hierarchy table.
EffectiveRights_Delete Boolean True Indicates whether a client can delete a folder or item.
EffectiveRights_Modify Boolean True Indicates whether a client can modify a folder or item.
EffectiveRights_Read Boolean True Indicates whether a client can read a folder or item.
EffectiveRights_ViewPrivateItems Boolean True Indicates whether a private item can be viewed.
LastModifiedName String True Contains the display name of the last user to modify an item.
LastModifiedTime Datetime True Indicates when an item was last modified.
IsAssociated Boolean False Indicates whether the item is associated with a folder.
WebClientReadFormQueryString String True Represents a URL to concatenate to the Microsoft Office Outlook Web App endpoint to read an item in Outlook Web App.
WebClientEditFormQueryString String True Represents a URL to concatenate to the Outlook Web App endpoint to edit an item in Outlook Web App.
ConversationId String True Contains the identifier of an item or conversation.
ConversationChangeKey String True Contains the change key of an item or conversation.
UniqueBody String False Represents an HTML fragment or plain-text which represents the unique body of this conversation.
UniqueBodyType String False Describes how the unique item body is stored in the item (HTML or TEXT).
Pseudo-Columns

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

Name Type Description
ImpersonationUser String The Identifier of the user to impersonate.
ImpersonationType String The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
SharedMailboxEmail String The email of a shared mailbox. Use this to retrieve the task items of a shared mailbox.

Stored Procedures

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

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

Jitterbit Connector for Microsoft Exchange Stored Procedures
Name Description
CreateAttachments Create and add a attachment to an existing email.
GetAttachment Retrieves the indicated attachments.
GetOAuthAccessToken Gets an authentication token from Outlook.
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. You will request the OAuthAccessToken from this URL.
GetUserOofSettings Provides access to the OOF settings of a user. A user is identified by the email address of the user. If the OOF message is null and OOF is enabled, no OOF message is sent.
MoveItem Move messages from one folder to another.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Office 365 services.
SendItem Send an email message that is located in the Exchange store.
SendMail Send a new email to the specified recipients.
CreateAttachments

Create and add a attachment to an existing email.

Stored Procedure Specific Information

Use CreateAttachments procedure to add an attachment to an existing email. To specify file paths of the attachments use Attachments input, whereas for base 64 encoded content specify AttachmentContent and AttachmentName.

EXECUTE CreateAttachments ItemId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD/FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAxEAAABDJHYjDdygQ5pQPUEu3S2cAAVZoayvAAAA', Attachments = 'C:\Users\User\Desktop\logfile.txt,C:\Users\User\Desktop\TestConnectionLog.txt'

Input

Name Type Required Description
ItemId String True The ID of the email item to add the attachment to.
Attachments String False The attachment to be added.
AttachmentContent String False The attachment content base 64 encoded.
AttachmentName String False The name of the file that will be created out of the attachment content.

Result Set Columns

Name Type Description
AttachmentIds String The ID of the attachment created
GetAttachment

Retrieves the indicated attachments.

Input
Name Type Required Description
AttachmentIds String True A semicolon-separated list of the Ids of the attachments to retrieve.
IncludeMimeContent String False Specifies whether the Multipurpose Internet Mail Extensions (MIME) content of an item or attachment is returned in the response. This element is optional.
BodyType String False Identifies how the body text is formatted in the response. This element is optional. Valid values are: Best, HTML, Text.
AttachmentFolder String False Folder to download attachments to.
Overwrite String False If true, conflicting files will be overwritten, otherwise they will be indexed. The default value is true.
Result Set Columns
Name Type Description
AttachmentId String Identifies the file attachment.
Name String Represents the name of the attachment.
ContentType String Describes the Multipurpose Internet Mail Extensions (MIME) type of the attachment content.
ContentId String Represents an identifier for the contents of an attachment. ContentId can be set to any string value. Applications can use ContentId to implement their own identification mechanisms.
ContentLocation String Contains the Uniform Resource Identifier (URI) that corresponds to the location of the content of the attachment.
Size String Represents the size in bytes of the file attachment.
LastModifiedTime String Represents when the file attachment was last modified.
IsInline String Represents whether the attachment appears in-line within an item.
IsContactPhoto String Indicates whether the file attachment is a contact picture.
Content String Contains the Base64-encoded contents of the file attachment.
GetOAuthAccessToken

Gets an authentication token from Outlook.

Input
Name Type Required Description
AuthMode String False The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is APP.
Scope String False A comma-separated list of permissions to request from the user. Please check the Microsoft Graph API for a list of available permissions. The default value is https://outlook.office.com/EWS.AccessAsUser.All offline_access.
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Outlook app settings. Only needed when the Authmode parameter is Web.
Verifier String False The verifier returned from Outlook after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Outlook authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Prompt String False Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with Outlook.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of Outlook.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.
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. You will request the OAuthAccessToken from this URL.

Input
Name Type Required Description
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Outlook app settings.
Scope String False A comma-separated list of scopes to request from the user. Please check the Microsoft Graph API documentation for a list of available permissions. The default value is https://outlook.office.com/EWS.AccessAsUser.All offline_access.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Outlook authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Prompt String False Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.
GetUserOofSettings

Provides access to the OOF settings of a user. A user is identified by the email address of the user. If the OOF message is null and OOF is enabled, no OOF message is sent.

Input
Name Type Required Description
User String False The email address of the user whose OOF settings to retreive.
ImpersonationUser String False The email address of the user to impersonate when requesting OOF settings.
ImpersonationType String False The type of Identifier used for the impersonation user (PrincipalName, SID, PrimarySmtpAddress, SmtpAddress).
Result Set Columns
Name Type Description
OofState String The user's Out of Office (OOF) state. (Disabled or Enabled or Scheduled)
ExternalAudience String The value that determines to whom external Out of Office (OOF) messages are sent. (None or Known or All)
StartTime String Represents the start of the time span set with an OOF status.
EndTime String Represents the end of the time span set with an OOF status.
InternalReply String The out of office (OOF) response sent to other users in the user's domain or trusted domains.
ExternalReply String The out of office (OOF) response that is sent to addresses outside the recipient's domain or trusted domains.
MoveItem

Move messages from one folder to another.

Input
Name Type Required Description
ItemId String False The unique ItemId of the item to be sent (required).
ItemChangekey String False The unique ItemChangeKey of the item to be sent (required).
FolderDisplayName String False The display name of the new folder.
Result Set Columns
Name Type Description
ResponseClass String The overall class of the response for the SendItem operation (Success/Warning/Error).
ResponseCode String A response code for the SendItem operation.
MessageText String Specific message text describing any errors or warning for the SendItem operation.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Office 365 services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Azure AD. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
SendItem

Send an email message that is located in the Exchange store.

Input
Name Type Required Description
ItemId String True The unique ItemId of the item to be sent (required).
ItemChangekey String True The unique ItemChangeKey of the item to be sent (required).
SaveToFolder String False Boolean value to determine whether or not to save the item.
SavedItemFolder String False The folder where the item will be saved. This value can be the name of any of the standard folders or the FolderId of a custom folder. Defaults to SentItems.
Result Set Columns
Name Type Description
ResponseClass String The overall class of the response for the SendItem operation (Success/Warning/Error).
ResponseCode String A response code for the SendItem operation.
MessageText String Specific message text describing any errors or warning for the SendItem operation.
SendMail

Send a new email to the specified recipients.

Stored Procedure Specific Information

You can send a mail with attachments, specifying one or more file paths or the base 64 content of the attachment. To specify file paths use Attachments input, whereas for base 64 encoded content specify AttachmentContent and AttachmentName. Note that if the file location specified in the 'Attachments' input is a folder, than all the files of the first level in that folder will be sent as attachments.

EXECUTE SendMail Subject = 'Issue with  Jitterbit Connector for Microsoft Exchange', Content = 'I am not being able to connect to Microsoft Exchange', Attachments = 'C:\Users\User\Desktop\logfile.txt,C:\Users\User\Desktop\TestConnectionLog.txt', ToRecipients = 'example@jbexample.com'

You can include CC and BCC recipients and send the mail. To specify one or more recipients, separate them using ','.

EXECUTE SendMail Subject = 'Issue with  Jitterbit Connector for Microsoft Exchange', Content = 'I am am not being able to connect to Microsoft Exchange', ToRecipients = 'example@jbexample.com', CcRecipients = 'test1@mail.com, test2@mail.com, test3@mail.com', BccRecipients = 'test1@mail.com'

Input

Name Type Required Description
Subject String True The email subject.
Content String True Email body content.
Attachments String False The attachments. Specify the filepaths of the files you want to attach in the following format: 'filepath1, filepath2'.
AttachmentContent String False The attachment content base 64 encoded.
AttachmentName String False The name of the file that will be created out of the attachment content.
ToRecipients String True The recipients. Specify recipients in the following format: 'person1@example.com, person2@example.com'.
CcRecipients String False The Cc: recipients for the message. Specify recipients in the following format: 'person1@example.com, person2@example.com'.
BccRecipients String False The Bcc: recipients for the message. Specify recipients in the following format: 'person1@example.com, person2@example.com'.

MSGraph Data Model

The Jitterbit Connector for Microsoft Exchange models Microsoft Exchange objects as relational tables and views. A Microsoft Exchange object has relationships to other objects; in the tables, these relationships are expressed through foreign keys. The following sections show the available API objects and provide more information on executing SQL to Microsoft Exchange APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Tables

The connector models the data in Microsoft Exchange as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Microsoft Exchange Tables
Name Description
CalendarGroups CalendarGroups table for Exchange data provider.
Calendars Calendars table for Exchange data provider.
Contacts Contacts table for Exchange data provider.
Events Events table for Exchange data provider.
ExtendedProperties Create, update, delete, and query Contacts items.
Groups Groups table for Exchange data provider.
MailFolders MailFolders table for Exchange data provider.
Messages Messages table for Exchange data provider.
Users Users table for Exchange data provider.
CalendarGroups

CalendarGroups table for Exchange data provider.

Columns
Name Type ReadOnly Description
id [KEY] String False
changeKey String False
classId String False
name String False
Pseudo-Columns

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

Name Type Description
userId String
Calendars

Calendars table for Exchange data provider.

Table Specific Information
Select

You can query Calendars by specifying an ID or selecting all:

SELECT * FROM Calendars WHERE ID = 'your Calendar ID goes here'

Select a certain column from the entity and filter by that column:

SELECT Name FROM Calendars WHERE Name LIKE 'John Calendar%'

Note: Unless specifying the AlwaysRequstTableDependencies connection property, or selecting/filtering by the reference CalendarGroupId, the value for CalendarGroupId will remain null.

Insert

Specify a Name as a minimum in order to create a new Calendar:

INSERT INTO Calendars (Name) VALUES ('John')
Columns
Name Type ReadOnly Description
calendarGroupId String False
id [KEY] String False
canEdit Bool False
canShare Bool False
canViewPrivateItems Bool False
changeKey String False
color String False
name String False
owner_name String False
owner_address String False
Pseudo-Columns

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

Name Type Description
userId String
Contacts

Contacts table for Exchange data provider.

Table Specific Information
Select

You can query Contacts by specifying an ID or selecting all:

SELECT * FROM Contacts WHERE ID = 'your Contact ID goes here'

Select a certain column from the entity and filter by that column:

SELECT GivenName FROM Contacts WHERE GivenName LIKE 'John%'
Insert

Specify a GivenName and a Surname as a minimum in order to create a new Contact:

INSERT INTO Contacts (GivenName, Surname) VALUES ('John', 'Smith')
Columns
Name Type ReadOnly Description
id [KEY] String False
categories String False
changeKey String False
createdDateTime Datetime False
lastModifiedDateTime Datetime False
assistantName String False
birthday Datetime False
businessAddress_street String False
businessAddress_city String False
businessAddress_state String False
businessAddress_countryOrRegion String False
businessAddress_postalCode String False
businessHomePage String False
businessPhones String False
children String False
companyName String False
department String False
displayName String False
emailAddresses String False
fileAs String False
generation String False
givenName String False
homeAddress_street String False
homeAddress_city String False
homeAddress_state String False
homeAddress_countryOrRegion String False
homeAddress_postalCode String False
homePhones String False
imAddresses String False
initials String False
jobTitle String False
manager String False
middleName String False
mobilePhone String False
nickName String False
officeLocation String False
otherAddress_street String False
otherAddress_city String False
otherAddress_state String False
otherAddress_countryOrRegion String False
otherAddress_postalCode String False
parentFolderId String False
personalNotes String False
profession String False
spouseName String False
surname String False
title String False
yomiCompanyName String False
yomiGivenName String False
yomiSurname String False
Pseudo-Columns

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

Name Type Description
userId String
Events

Events table for Exchange data provider.

Table Specific Information
Select

You can retrieve all from Events, specify an Event (Id), CalendarId, or you can filter results by a certain column.

Note: Unless specifying the AlwaysRequstTableDependencies connection property, or selecting/filtering by the reference columns CalendarId, the value for CalendarId will remain null.

Note: To retrieve all Events for Other user (Works only in case of OAuthGrantType=CLIENT), you can specify the query like -

SELECT * FROM Events WHERE UserId = '0409f710-2aa9-4f05-8944-ef382160f1d1' AND CalendarId IN (SELECT ID FROM Calendars WHERE UserId = '0409f710-2aa9-4f05-8944-ef382160f1d1')
Insert

To create a new event, start and end are required, including the timezone.

INSERT INTO Events (Subject, Body_Content, Start_DateTime, Start_TimeZone, End_DateTime, End_TimeZone) VALUES ('New Test Event', 'Event created using Office365Provider', '2016-01-01T10:00:00', 'UTC', '2016-01-01T11:00:00', 'UTC')

Note: By default this statement will create your event under the default calendar.

Columns
Name Type ReadOnly Description
id [KEY] String False
categories String False
changeKey String False
createdDateTime Datetime False
lastModifiedDateTime Datetime False
attendees String False
body_contentType String False
body_content String False
bodyPreview String False
end_dateTime String False
end_timeZone String False
hasAttachments Bool False
iCalUId String False
importance String False
isAllDay Bool False
isCancelled Bool False
isOrganizer Bool False
isReminderOn Bool False
location_displayName String False
location_locationEmailAddress String False
location_address_street String False
location_address_city String False
location_address_state String False
location_address_countryOrRegion String False
location_address_postalCode String False
location_coordinates_altitude Double False
location_coordinates_latitude Double False
location_coordinates_longitude Double False
location_coordinates_accuracy Double False
location_coordinates_altitudeAccuracy Double False
location_locationUri String False
location_locationType String False
location_uniqueId String False
location_uniqueIdType String False
locations String False
onlineMeetingUrl String False
organizer_emailAddress_name String False
organizer_emailAddress_address String False
originalEndTimeZone String False
originalStart Datetime False
originalStartTimeZone String False
recurrence_pattern_type String False
recurrence_pattern_interval Int False
recurrence_pattern_month Int False
recurrence_pattern_dayOfMonth Int False
recurrence_pattern_daysOfWeek String False
recurrence_pattern_firstDayOfWeek String False
recurrence_pattern_index String False
recurrence_range_type String False
recurrence_range_startDate Date False
recurrence_range_endDate Date False
recurrence_range_recurrenceTimeZone String False
recurrence_range_numberOfOccurrences Int False
reminderMinutesBeforeStart Int False
responseRequested Bool False
responseStatus_response String False
responseStatus_time Datetime False
sensitivity String False
seriesMasterId String False
showAs String False
start_dateTime String False
start_timeZone String False
subject String False
transactionId String False
type String False
webLink String False
calendarId String False
Pseudo-Columns

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

Name Type Description
groupId String
userId String
ExtendedProperties

Create, update, delete, and query Contacts items.

Table Specific Information
Select

You can query ExtendedProperties for a specific resource from the following resources: Events, Calendars and Messages. In order to get the value for an extended property you need to specify: - Entity field, wether it is Message, Event or Calendar - EntityId, the ID of the resource - Id, the ID of the extended property.

SELECT * FROM ExtendedProperties WHERE Entity = 'Event' AND EntityId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAw8AAABDJHYjDdygQ5pQPUEu3S2cAAZq-GA0AAAA' AND ID = 'String {66f5a359-4659-4830-9070-00047ec6ac6e} Name Color'
Insert

You can create an extended property in a resource, by specifying Entity, EntityId, ID of the extended property and its Value.

INSERT INTO ExtendedProperties (Entity, EntityId, Id, Value) VALUES ('Calendar', 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAASHbEoeAAAA', 'String {66f5a359-4659-4830-9070-00047ec6ac6e} Name Color', 'Yellow')
Columns
Name Type ReadOnly Description
Id String False The Identifier of the extended property
Value String False The value of the extended property
Entity String False The entity type.
EntityId String False The entity ID that the extended properties belong to.
Groups

Groups table for Exchange data provider.

Table Specific Information

Groups require Administrator permissions. To work with them, you must create your own custom OAuth App and set the appropriate OAuthClientId and OAuthClientSecret. In this app, you must configure it to request the Group.Read.All and the Group.ReadWrite.All permissions. This can be done at https://apps.dev.microsoft.com, or in the App Registrations panel at http://portal.azure.com.

To authorize Groups permissions, an administrator must grant the Groups permissions for your organization at large. This can be done via the administrator authorization endpoint. Simply have the administrator navigate to the following web page and grant permissions. Then run the OAuth authorization as normal afterwards.

https://login.microsoftonline.com/common/adminconsent?client_id=[YourClientId]&redirect_uri=http://localhost:33333

Note that if your organization has multiple tenants, you may replace the /common/ in the URL with the tenant ID to indicate which tenant to grant permissions for.

Select

Retrieve all groups, specify a GroupId (Id), or simply filter by a certain column:

SELECT * FROM Groups WHERE Id = 'Group Id here'
SELECT Id, Description, DisplayName FROM Groups WHERE Name = 'test'
Insert

The following are required to create a new Security Group:

INSERT INTO Groups (DisplayName, MailEnabled, MailNickname, SecurityEnabled) VALUES ('Test group', false, 'test', true)
Columns
Name Type ReadOnly Description
id [KEY] String False
deletedDateTime Datetime False
allowExternalSenders Bool False
assignedLicenses String False
autoSubscribeNewMembers Bool False
classification String False
createdDateTime Datetime False
description String False
displayName String False
groupTypes String False
hasMembersWithLicenseErrors Bool False
isArchived Bool False
isSubscribedByMail Bool False
licenseProcessingState_state String False
mail String False
mailEnabled Bool False
mailNickname String False
onPremisesLastSyncDateTime Datetime False
onPremisesProvisioningErrors String False
onPremisesSecurityIdentifier String False
onPremisesSyncEnabled Bool False
preferredDataLocation String False
proxyAddresses String False
renewedDateTime Datetime False
securityEnabled Bool False
unseenCount Int False
visibility String False
MailFolders

MailFolders table for Exchange data provider.

Columns
Name Type ReadOnly Description
id [KEY] String False
childFolderCount Int False
displayName String False
parentFolderId String False
totalItemCount Int False
unreadItemCount Int False
Pseudo-Columns

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

Name Type Description
userId String
Messages

Messages table for Exchange data provider.

Table Specific Information
Select

You can retrieve all from Messages, specify a Message (Id), ParentFolderId, or you can filter results by a certain column:

SELECT * FROM Messages WHERE Id = 'MyMessageId'

SELECT * FROM Messages WHERE ParentFolderId = 'MyParentfolderId'

SELECT * FROM Messages WHERE ParentFolderId = 'Drafts'

Note: Unless specifying the AlwaysRequstTableDependencies connection property, or selecting/filtering by the reference columns ParentFolderId, the value for ParentFolderId will remain null.

Insert

After the insert a new Message will be created in the User's Drafts folder.

INSERT INTO Messages (Subject, Body_Content) VALUES ('New test Email', 'Test Email created.')
Columns
Name Type ReadOnly Description
id [KEY] String False
categories String False
changeKey String False
createdDateTime Datetime False
lastModifiedDateTime Datetime False
bccRecipients String False
body_contentType String False
body_content String False
bodyPreview String False
ccRecipients String False
conversationId String False
flag_completedDateTime_dateTime String False
flag_completedDateTime_timeZone String False
flag_flagStatus String False
from_emailAddress_name String False
from_emailAddress_address String False
hasAttachments Bool False
importance String False
inferenceClassification String False
internetMessageHeaders String False
internetMessageId String False
isDeliveryReceiptRequested Bool False
isDraft Bool False
isRead Bool False
isReadReceiptRequested Bool False
parentFolderId String False
receivedDateTime Datetime False
replyTo String False
sender_emailAddress_name String False
sender_emailAddress_address String False
sentDateTime Datetime False
subject String False
toRecipients String False
uniqueBody_contentType String False
uniqueBody_content String False
webLink String False
Pseudo-Columns

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

Name Type Description
userId String
Users

Users table for Exchange data provider.

Table Specific Information
Select

Query the Users table by retrieving everything from Users, specifying a Id, or filtering by a column:

SELECT * FROM Users WHERE Id = '616391f0-32d8-4127-8f25-aa55771d6617'

SELECT DisplayName, ID FROM Users WHERE DisplayName LIKE 'John%'
Insert

The following are required to create a new organizational User:

INSERT INTO Users (AccountEnabled, DisplayName, MailNickname, UserPrincipalName, PasswordProfile_ForceChangePasswordNextSignIn, PasswordProfile_Password) VALUES (false, 'John Smith', 'JohnS', 'smithjohn@yourcompanydomain.com', true, '123password')
Columns
Name Type ReadOnly Description
id [KEY] String False
deletedDateTime Datetime False
aboutMe String False
accountEnabled Bool False
ageGroup String False
assignedLicenses String False
assignedPlans String False
birthday Datetime False
businessPhones String False
city String False
companyName String False
consentProvidedForMinor String False
country String False
department String False
deviceEnrollmentLimit Int False
displayName String False
employeeId String False
faxNumber String False
givenName String False
hireDate Datetime False
imAddresses String False
interests String False
isResourceAccount Bool False
jobTitle String False
legalAgeGroupClassification String False
licenseAssignmentStates String False
mail String False
mailboxSettings_automaticRepliesSetting_status String False
mailboxSettings_automaticRepliesSetting_externalAudience String False
mailboxSettings_automaticRepliesSetting_scheduledStartDateTime_dateTime String False
mailboxSettings_automaticRepliesSetting_scheduledStartDateTime_timeZone String False
mailboxSettings_automaticRepliesSetting_internalReplyMessage String False
mailboxSettings_automaticRepliesSetting_externalReplyMessage String False
mailboxSettings_archiveFolder String False
mailboxSettings_timeZone String False
mailboxSettings_language_locale String False
mailboxSettings_language_displayName String False
mailboxSettings_workingHours_daysOfWeek String False
mailboxSettings_workingHours_startTime Time False
mailboxSettings_workingHours_endTime Time False
mailboxSettings_workingHours_timeZone_name String False
mailNickname String False
mobilePhone String False
mySite String False
officeLocation String False
onPremisesDistinguishedName String False
onPremisesDomainName String False
onPremisesExtensionAttributes_extensionAttribute1 String False
onPremisesExtensionAttributes_extensionAttribute2 String False
onPremisesExtensionAttributes_extensionAttribute3 String False
onPremisesExtensionAttributes_extensionAttribute4 String False
onPremisesExtensionAttributes_extensionAttribute5 String False
onPremisesExtensionAttributes_extensionAttribute6 String False
onPremisesExtensionAttributes_extensionAttribute7 String False
onPremisesExtensionAttributes_extensionAttribute8 String False
onPremisesExtensionAttributes_extensionAttribute9 String False
onPremisesExtensionAttributes_extensionAttribute10 String False
onPremisesExtensionAttributes_extensionAttribute11 String False
onPremisesExtensionAttributes_extensionAttribute12 String False
onPremisesExtensionAttributes_extensionAttribute13 String False
onPremisesExtensionAttributes_extensionAttribute14 String False
onPremisesExtensionAttributes_extensionAttribute15 String False
onPremisesImmutableId String False
onPremisesLastSyncDateTime Datetime False
onPremisesProvisioningErrors String False
onPremisesSamAccountName String False
onPremisesSecurityIdentifier String False
onPremisesSyncEnabled Bool False
onPremisesUserPrincipalName String False
otherMails String False
passwordPolicies String False
passwordProfile_password String False
passwordProfile_forceChangePasswordNextSignIn Bool False
passwordProfile_forceChangePasswordNextSignInWithMfa Bool False
pastProjects String False
postalCode String False
preferredLanguage String False
preferredName String False
provisionedPlans String False
proxyAddresses String False
responsibilities String False
schools String False
showInAddressList Bool False
signInSessionsValidFromDateTime Datetime False
skills String False
state String False
streetAddress String False
surname String False
usageLocation String False
userPrincipalName String False
userType String False

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 Microsoft Exchange Views
Name Description
CalendarView Retrieve the ccurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's.
EventAttachments EventAttachments View for Exchange data provider.
EventInstances EventInstances View for Exchange data provider.
GroupMembers Group Members table for Exchange data provider.
MessageAttachments MessageAttachments View for Exchange data provider.
CalendarView

Retrieve the ccurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's.

Table Specific Information
Select

Get the occurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's. By default only the event occurrences from the user's default calendar in the range of the last 30 days are returned. You can filter results by CalendarId, Start_DateTime, End_DateTime.

For example the following queries are processed server-side:

SELECT * FROM CalendarView WHERE Start_DateTime >= '2019-12-10 15:00' AND End_DateTime <= '2020-01-10 14:30'
SELECT * FROM CalendarView WHERE CalendarId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAAACC_IAAAA='

NOTE Unless specifying the AlwaysRequestTableDependencies connection property or selecting/filtering by the reference column CalendarId, the value for CalendarId remains null.

Columns
Name Type Description
id [KEY] String
categories String
changeKey String
createdDateTime Datetime
lastModifiedDateTime Datetime
attendees String
body_contentType String
body_content String
bodyPreview String
end_dateTime String
end_timeZone String
hasAttachments Bool
iCalUId String
importance String
isAllDay Bool
isCancelled Bool
isOrganizer Bool
isReminderOn Bool
location_displayName String
location_locationEmailAddress String
location_address_street String
location_address_city String
location_address_state String
location_address_countryOrRegion String
location_address_postalCode String
location_coordinates_altitude Double
location_coordinates_latitude Double
location_coordinates_longitude Double
location_coordinates_accuracy Double
location_coordinates_altitudeAccuracy Double
location_locationUri String
location_locationType String
location_uniqueId String
location_uniqueIdType String
locations String
onlineMeetingUrl String
organizer_emailAddress_name String
organizer_emailAddress_address String
originalEndTimeZone String
originalStart Datetime
originalStartTimeZone String
recurrence_pattern_type String
recurrence_pattern_interval Int
recurrence_pattern_month Int
recurrence_pattern_dayOfMonth Int
recurrence_pattern_daysOfWeek String
recurrence_pattern_firstDayOfWeek String
recurrence_pattern_index String
recurrence_range_type String
recurrence_range_startDate Date
recurrence_range_endDate Date
recurrence_range_recurrenceTimeZone String
recurrence_range_numberOfOccurrences Int
reminderMinutesBeforeStart Int
responseRequested Bool
responseStatus_response String
responseStatus_time Datetime
sensitivity String
seriesMasterId String
showAs String
start_dateTime String
start_timeZone String
subject String
type String
webLink String
calendarId String
EventAttachments

EventAttachments View for Exchange data provider.

Table Specific Information
Select

You can query EventAttachments by specifying the Event ID (Requried):

SELECT * FROM [EventAttachments] WHERE EventId = 'event id'

This query will get the Attachments of the specified Event as a list without including their content.

Columns
Name Type Description
eventId String
id [KEY] String
contentType String
isInline Bool
lastModifiedDateTime Datetime
name String
size Int
userId String
EventInstances

EventInstances View for Exchange data provider.

Table Specific Information
Select

You can query EventInstances by specifying the Event Id, StartDatetime and EndDateTime. EventId is a required field, instead StartDatetime and EndDateTime have a default range of the last 30 days. If you query filtering only by EventId and the specific event does not exist within this time range, you will get empty results.

SELECT * FROM [EventInstances] WHERE ID = 'event id' AND StartDateTime = '2018/01/01' AND EndDateTime = '2018/12/31'
SELECT * FROM [EventInstances] WHERE ID = 'event id'

By default, if StartDateTime and EndDateTime filters are not specified, only the event instances from the user's default calendar in the range of the last 30 days will be returned. Otherwise, the query will get the instances of the Event during the period specified by StartDateTime and EndDateTime.

Columns
Name Type Description
eventId String
id [KEY] String
categories String
changeKey String
createdDateTime Datetime
lastModifiedDateTime Datetime
attendees String
body_contentType String
body_content String
bodyPreview String
end_dateTime String
end_timeZone String
hasAttachments Bool
iCalUId String
importance String
isAllDay Bool
isCancelled Bool
isOrganizer Bool
isReminderOn Bool
location_displayName String
location_locationEmailAddress String
location_address_street String
location_address_city String
location_address_state String
location_address_countryOrRegion String
location_address_postalCode String
location_coordinates_altitude Double
location_coordinates_latitude Double
location_coordinates_longitude Double
location_coordinates_accuracy Double
location_coordinates_altitudeAccuracy Double
location_locationUri String
location_locationType String
location_uniqueId String
location_uniqueIdType String
locations String
onlineMeetingUrl String
organizer_emailAddress_name String
organizer_emailAddress_address String
originalEndTimeZone String
originalStart Datetime
originalStartTimeZone String
recurrence_pattern_type String
recurrence_pattern_interval Int
recurrence_pattern_month Int
recurrence_pattern_dayOfMonth Int
recurrence_pattern_daysOfWeek String
recurrence_pattern_firstDayOfWeek String
recurrence_pattern_index String
recurrence_range_type String
recurrence_range_startDate Date
recurrence_range_endDate Date
recurrence_range_recurrenceTimeZone String
recurrence_range_numberOfOccurrences Int
reminderMinutesBeforeStart Int
responseRequested Bool
responseStatus_response String
responseStatus_time Datetime
sensitivity String
seriesMasterId String
showAs String
start_dateTime String
start_timeZone String
subject String
type String
webLink String
GroupMembers

Group Members table for Exchange data provider.

Table Specific Information

Groups require Administrator permissions. To work with them, you must create your own custom OAuth App and set the appropriate OAuthClientId and OAuthClientSecret. In this app, you must configure it to request the GroupMember.Read.All and the GroupMember.ReadWrite.All permissions. This can be done at https://apps.dev.microsoft.com, or in the App Registrations panel at http://portal.azure.com.

To authorize GroupMembers permissions, an administrator must grant the GroupMembers permissions for your organization at large. This can be done via the administrator authorization endpoint. Simply have the administrator navigate to the following web page and grant permissions. Then run the OAuth authorization as normal afterwards.

https://login.microsoftonline.com/common/adminconsent?client_id=[YourClientId]&redirect_uri=http://localhost:33333

Note that if your organization has multiple tenants, you may replace the /common/ in the URL with the tenant ID to indicate which tenant to grant permissions for.

Select

The connector uses the Microsoft Exchange API to process WHERE clause conditions. The rest of the filter is executed client-side in the connector.

For example, the following query is processed server-side:

SELECT * FROM GroupMembers
Columns
Name Type Description
Id [KEY] String
GroupId String
AssistantName String
BusinessPhones String
Classification String
CreatedDateTime Datetime
CreationOptions String
DeletedDateTime Datetime
Description String
DisplayName String
ExpirationDateTime Datetime
GivenName String
GroupTypes String
IsAssignableToRole Boolean
JobTitle String
Mail String
MailEnabled Bool
MailNickname String
MembershipRule String
MembershipRuleProcessingState String
MobilePhone String
OfficeLocation String
OnPremisesDomainName String
OnPremisesLastSyncDateTime Datetime
OnPremisesNetBiosName String
OnPremisesProvisioningErrors String
OnPremisesSamAccountName String
OnPremisesSecurityIdentifier String
OnPremisesSyncEnabled Bool
PreferredDataLocation String
PreferredLanguage String
ProxyAddresses String
RenewedDateTime Datetime
SecurityEnabled Bool
Surname String
UserPrincipalName String
Visibility String
MessageAttachments

MessageAttachments View for Exchange data provider.

Table Specific Information
Select

You can query MessageAttachments by specifying the Message ID (Required):

SELECT * FROM [MessageAttachments] WHERE MessageId = 'message id'
SELECT * FROM [MessageAttachments] WHERE UserId = '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe' AND ID = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAw4AAABDJHYjDdygQ5pQPUEu3S2cAAhJYnE3AAAAARIAEADUFdfqaYanT5_pTPvzgMYh' AND MessageId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAw4AAABDJHYjDdygQ5pQPUEu3S2cAAhJYnE3AAAA'

This query will get the Attachments of the specified Message as a list without including their content.

Columns
Name Type Description
messageId String
id [KEY] String
contentType String
isInline Bool
lastModifiedDateTime Datetime
name String
size Int
userId String

Stored Procedures

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

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

Jitterbit Connector for Microsoft Exchange Stored Procedures
Name Description
AddAttachments Add attachment(s) to a message.
DeleteAttachment Delete attachment.
DismissEventReminder Dismiss the reminder for an Event.
DownloadAttachments Download the attachments of an email
ForwardMail Retrieve Forwarded Mail.
GetAdminConsentURL Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.
GetOAuthAccessToken Gets an authentication token from Outlook.
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. You will request the OAuthAccessToken from this URL.
MoveMail Move mail.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various Office 365 services.
ReplyToMessage Reply to a Message.
RespondToEvent Respond to an Event.
SendMail Send mail.
SnoozeEventReminder Snooze the reminder for an Event.
AddAttachments

Add attachment(s) to a message.

Input
Name Type Required Description
Id String False The ID of the message to add the attachment to.
DestinationType String False The destination object type where the attachment is being added. Allowed values are; Message or Event.
FileName String False Name of the email attachment.
LocalFile String False The file containing the content of the attachment
ContentBytes String False Content of the attachment encoded to base 64.
Result Set Columns
Name Type Description
ContentBytes String Added Successfully - if the attachment content was added to the message/event.
Id String ID of the attachment that was added.
LastModifiedDateTime Datetime Last modified date time of attachment that was added.
Isinline Boolean Whether the attachment that was added is inline.
Name String Name of the attachment that was added.
Contenttype String Content-type of the attachment that was added.
Size Int Size of the attachment that was added.
DeleteAttachment

Delete attachment.

Input
Name Type Required Description
MessageId String True The email Id.
AttachmentID String True ID of the attachment
Result Set Columns
Name Type Description
Id String ID of the message that was deleted.
DismissEventReminder

Dismiss the reminder for an Event.

Input
Name Type Required Description
EventId String True The event of the ID you are dismissing.
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.
DownloadAttachments

Download the attachments of an email

Input
Name Type Required Description
AttachmentSource String True Source to retrieve an attachment from. Allowed values: Message, Event.
SourceId String True ID of the email or event from which you want to get the attachments.
AttachmentId String False ID of the attachment you want to download. Returns all the attachments of the email if not specified. If DownloadInEmlFormat is specified, this is not required.
DownloadTo String False Where to store the attachments. If not specified, it returns the content bytes of the attachment.
UserId String False ID of the Impersonated User.
DownloadInEmlFormat String False Whether the attachment should be downloaded in .eml format The default value is false.
Encoding String False The ContentBytes input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
Id String ID of the downloaded attachment.
Name String Name of the downloaded attachment.
ContentBytes String Content of the attachment.
LastmodifiedDatetime String Last modified date of the attachment.
ContentType String Content type of the attachment. If DownloadTo is specified this field will be null.
ForwardMail

Retrieve Forwarded Mail.

Input
Name Type Required Description
MessageId String True ID of the email.
ToRecipients String True A semicolon-separated list of recipient emails.
Comment String False BodyContent on top of the original message.
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.
GetAdminConsentURL

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input
Name Type Required Description
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Reply URL in the Azure AD app settings.
State String False The same value for state that you sent when you requested the authorization code.
Scope String False A space-separated list of scopes to request from the Admin. Please check the Microsoft Graph API documentation for a list of available permissions. The default value is https://graph.microsoft.com/calendars.readwrite.Shared https://graph.microsoft.com/Contacts.ReadWrite https://graph.microsoft.com/Group.Read.All https://graph.microsoft.com/Group.ReadWrite.All https://graph.microsoft.com/User.ReadWrite.All https://graph.microsoft.com/Mail.ReadWrite.Shared https://graph.microsoft.com/Reports.Read.All.
Result Set Columns
Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.
GetOAuthAccessToken

Gets an authentication token from Outlook.

Input
Name Type Required Description
AuthMode String False The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is APP.
Scope String False A space-separated list of permissions to request from the user. Please check the Microsoft Graph API for a list of available permissions. When OAuthGrantType='CLIENT', a scope of 'https://graph.microsoft.com/.default' is used. '/.default' picks up whatever permissions your app already has. The default value is offline_access https://graph.microsoft.com/calendars.readwrite.Shared https://graph.microsoft.com/Contacts.ReadWrite https://graph.microsoft.com/Group.Read.All https://graph.microsoft.com/Group.ReadWrite.All https://graph.microsoft.com/User.ReadWrite.All https://graph.microsoft.com/Mail.ReadWrite.Shared https://graph.microsoft.com/Reports.Read.All.
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Outlook app settings. Only needed when the Authmode parameter is Web.
Verifier String False The verifier returned from Outlook after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Outlook authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Prompt String False Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with Outlook.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of Outlook.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.
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. You will request the OAuthAccessToken from this URL.

Input
Name Type Required Description
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Outlook app settings.
Scope String False A space-separated list of scopes to request from the user when OAuthGrantType='CODE'. Please check the Microsoft Graph API documentation for a list of available permissions. When OAuthGrantType='CLIENT', a scope of 'https://graph.microsoft.com/.default' is used. '/.default' picks up whatever permissions your app already has. The default value is offline_access https://graph.microsoft.com/calendars.readwrite.Shared https://graph.microsoft.com/Contacts.ReadWrite https://graph.microsoft.com/Group.Read.All https://graph.microsoft.com/Group.ReadWrite.All https://graph.microsoft.com/User.ReadWrite.All https://graph.microsoft.com/Mail.ReadWrite.Shared https://graph.microsoft.com/Reports.Read.All.
State String False Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the Outlook authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Prompt String False Defaults to 'select_account' which prompts the user to select account while authenticating. Set to 'None', for no prompt, 'login' to force user to enter their credentials or 'consent' to trigger the OAuth consent dialog after the user signs in, asking the user to grant permissions to the app.
Result Set Columns
Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.
MoveMail

Move mail.

Input
Name Type Required Description
MessageId String True The email Id.
DestinationId String True ID of the destination folder where the email will be moved to.
Result Set Columns
Name Type Description
Id String ID of the message that was moved.
RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Office 365 services.

Input
Name Type Required Description
OAuthRefreshToken String True The refresh token returned from the original authorization code exchange.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from Azure AD. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String A token that may be used to obtain a new access token.
ExpiresIn String The remaining lifetime on the access token.
ReplyToMessage

Reply to a Message.

Input
Name Type Required Description
MessageId String True ID of the email.
Comment String False BodyContent on top of the original message.
ToAll String False Reply to all or not. The default value is false.
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.
RespondToEvent

Respond to an Event.

Input
Name Type Required Description
EventId String True The event of the ID you are responding to.
ResponseType String True The type of the response to this event. Allowed values: Accept, Decline
SendResponse String False True if a response is to be sent to the organizer; otherwise, false. Optional. Default is true. The default value is true.
Comment String False Text included in the response. Optional.
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.
SendMail

Send mail.

Input
Name Type Required Description
Id String False The ID of the message to be sent (this is optional). The user can either use an existing email ID or send a new email by optionally filling the other fields.
Subject String False The email subject.
Content String False Email body content.
Attachments String False The attachments. Specify File attachments in the following format: filename1, filecontent1;filename2, filecontent2. Each filecontent can be either base64 data, or the path of a local file with the @ character before it
FileName String False Name of the email attachment.
LocalFile String False The file containing the content of the attachment
ContentBytes String False Content of the attachment encoded to base 64.
ToRecipients String False The recipients. Specify recipients in the following order: person1@example.com;person2@example.com.
CcRecipients String False The Cc: recipients for the message. Specify recipients in the following order: person1@example.com;person2@example.com.
BccRecipients String False The Bcc: recipients for the message. Specify recipients in the following order: person1@example.com;person2@example.com.
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.
SnoozeEventReminder

Snooze the reminder for an Event.

Input
Name Type Required Description
EventId String True The event of the ID you are dismissing.
DateTime String False The new date-time to postpone the event reminder to.
TimeZone String False The TimeZone for the chosen date-time
Result Set Columns
Name Type Description
Success Boolean If the request is sucessful.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft Exchange:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Contacts table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Contacts'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the GetAttachment stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetAttachment' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Contacts table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Contacts'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:exchange:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
Platform The Platform associated with the Exchange server.
Schema Specify the Microsoft Exchange schema.
AuthScheme The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate, OAuth, AzureAD, AzureServicePrincipal and AzureMSI.
Server The address of the Exchange server to which you are connecting.
User The user who is authenticating to the Exchange site.
Password The password used to authenticate to the Exchange site.

Azure Authentication

Property Description
AzureTenant The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthGrantType The grant type for the OAuth flow.
UserId Specify this UserId in order to access Outlook resources for a specific user. Required when OAuthGrantType is set to 'CLIENT'.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

JWT OAuth

Property Description
OAuthJWTCert The JWT Certificate store.
OAuthJWTCertType The type of key store containing the JWT Certificate.
OAuthJWTCertPassword The password for the OAuth JWT certificate.
OAuthJWTCertSubject The subject of the OAuth JWT certificate.

Kerberos

Property Description
KerberosKDC The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm The Kerberos Realm used to authenticate the user.
KerberosSPN The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm The Kerberos realm of the service.
KerberosServiceKDC The Kerberos KDC of the service.
KerberosTicketCache The full file path to an MIT Kerberos credential cache file.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
AlwaysRequestTableDependencies Control whether you want to always retrieve Table Dependencies (Foreign Keys). Setting this to TRUE might slow down queries and increase amount of calls made.
BodyType The BodyType element identifies how the body text is formatted in the response. The possible options are: Best - The response will return the richest available content of body text, HTML - The response will return an item body as HTML, Text - The response will return an item body as plain text. The default is set to Best.
CustomHeaders Other headers as determined by the user (optional).
DirectoryRetrievalDepth Depth level of folder to query Folders and Items.
DisableServerSideFiltering Control whether you want to disable server-side filtering. By default this property is set to False, and server-side filtering is attempted for all columns/tables.
GroupId Specify this GroupId in order to access the OneNote documents for this group.
ImpersonationType The type of identifier to use with impersonation while sending requests to the Exchange site.
ImpersonationUser The user to impersonate while sending requests to the Exchange site.
IncludeContent A boolean indicating if additional content should be retrieved.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Microsoft Exchange.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
Platform The Platform associated with the Exchange server.
Schema Specify the Microsoft Exchange schema.
AuthScheme The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate, OAuth, AzureAD, AzureServicePrincipal and AzureMSI.
Server The address of the Exchange server to which you are connecting.
User The user who is authenticating to the Exchange site.
Password The password used to authenticate to the Exchange site.

Platform

The Platform associated with the Exchange server.

Possible Values

Exchange2010_SP2, Exchange2007, Exchange2013, Exchange2007_SP1, Exchange2013_SP1, Exchange2010, Exchange_Online, Exchange2010_SP1

Data Type

string

Default Value

Exchange_Online

Remarks

The Platform associated with the Exchange server.

Property Description
Exchange2007\* Target the initial release version of Exchange 2007.
Exchange2007_SP1\* Target Exchange 2007 Service Pack 1 (SP1), Exchange 2007 Service Pack 2 (SP2), and Exchange 2007 Service Pack 3 (SP3).
Exchange2010 Target Exchange 2010.
Exchange2010_SP1 Target Exchange 2010 Service Pack 1 (SP1).
Exchange2010_SP2 Target Exchange 2010 Service Pack 2 (SP2) and Exchange 2010 Service Pack 3 (SP3).
Exchange2013 Target Exchange 2013.
Exchange2013_SP1 Target Exchange 2013 Service Pack 1 (SP1).
Exchange_Online Target Exchange Online.

\* - These APIs do not support filtering using the WHERE clause.

Schema

Specify the Microsoft Exchange schema.

Possible Values

EWS, MSGraph

Data Type

string

Default Value

EWS

Remarks

The schemas available are EWS and MSGraph.

AuthScheme

The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate, OAuth, AzureAD, AzureServicePrincipal and AzureMSI.

Possible Values

Basic, AzureServicePrincipalCert, OAuth, NONE, NTLM, AzureAD, Digest, AzureMSI, Negotiate, AzureServicePrincipal

Data Type

string

Default Value

Basic

Remarks

Together with Password and User, this field is used to authenticate against the server. Basic is the default option. Use the following options to select your authentication scheme:

  • NTLM: Set this to use your Windows credentials for authentication.
  • Basic: Set this to use HTTP Basic authentication.
  • Digest: Set this to use HTTP Digest authentication.
  • Negotiate: If AuthScheme is set to Negotiate, the connector will negotiate an authentication mechanism with the server. Set AuthScheme to Negotiate to use Kerberos authentication.
  • OAuth: OAuth 2.0 is only supported for Exchange Online platform.
  • AzureAD: Set this to perform Azure Active Directory OAuth authentication. This authentication scheme is applicable only for the Exchange_Online Platform.
  • AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM. This authentication scheme is applicable only for the Exchange_Online Platform.
  • AzureServicePrincipal: Set this to authenticate as an Azure Service Principal. This authentication scheme is applicable only for the Exchange_Online Platform.
  • AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal Certificate. This authentication scheme is applicable only for the Exchange_Online Platform.

Server

The address of the Exchange server to which you are connecting.

Data Type

string

Default Value

""

Remarks

This should be set to the Exchange Web Services URL. For Exchange Online, you should set it to https://outlook.office365.com/EWS/Exchange.asmx.

User

The user who is authenticating to the Exchange site.

Data Type

string

Default Value

""

Remarks

The username used to authenticate to the Exchange site. NOTE: According to Exchange, Basic authentication (using User and Password) will be deprecated soon.

Password

The password used to authenticate to the Exchange site.

Data Type

string

Default Value

""

Remarks

The password used to authenticate to the Exchange site. NOTE: According to Exchange, Basic authentication (using User and Password) will be deprecated soon.

Azure Authentication

This section provides a complete list of Azure authentication properties you can configure.

Property Description
AzureTenant The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

AzureTenant

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Data Type

string

Default Value

""

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory ID in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthGrantType The grant type for the OAuth flow.
UserId Specify this UserId in order to access Outlook resources for a specific user. Required when OAuthGrantType is set to 'CLIENT'.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Linux ~/.config

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthGrantType

The grant type for the OAuth flow.

Possible Values

CODE, CLIENT, PASSWORD

Data Type

string

Default Value

CLIENT

Remarks

The following options are available: CODE,CLIENT,PASSWORD

UserId

Specify this UserId in order to access Outlook resources for a specific user. Required when OAuthGrantType is set to 'CLIENT'.

Data Type

string

Default Value

""

Remarks

Specify this UserId in order to access Outlook resources for a specific user. Required when OAuthGrantType is set to 'CLIENT'.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

JWT OAuth

This section provides a complete list of JWT OAuth properties you can configure.

Property Description
OAuthJWTCert The JWT Certificate store.
OAuthJWTCertType The type of key store containing the JWT Certificate.
OAuthJWTCertPassword The password for the OAuth JWT certificate.
OAuthJWTCertSubject The subject of the OAuth JWT certificate.

OAuthJWTCert

The JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The name of the certificate store for the client certificate.

The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject

field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

Property Description
MY A certificate store holding personal certificates with their associated private keys.
CA Certifying authority certificates.
ROOT Root certificates.
SPC Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

OAuthJWTCertType

The type of key store containing the JWT Certificate.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_FILE, SSHPUBLIC_KEY_BLOB, P7BFILE, PPKFILE, XMLFILE, XMLBLOB

Data Type

string

Default Value

USER

Remarks

This property can take one of the following values:

Property Description
USER For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
MACHINE For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFILE The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBLOB The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKEY_FILE The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFILE The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB The certificate store is a string that contains a certificate in XML format.

OAuthJWTCertPassword

The password for the OAuth JWT certificate.

Data Type

string

Default Value

""

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.

OAuthJWTCertSubject

The subject of the OAuth JWT certificate.

Data Type

string

Default Value

*

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.

If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=example@jbexample.com". Common fields and their meanings are displayed below.

Field Meaning
CN Common Name. This is commonly a host name like www.server.com.
O Organization
OU Organizational Unit
L Locality
S State
C Country
E Email Address

If a field value contains a comma it must be quoted.

Kerberos

This section provides a complete list of Kerberos properties you can configure.

Property Description
KerberosKDC The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosRealm The Kerberos Realm used to authenticate the user.
KerberosSPN The service principal name (SPN) for the Kerberos Domain Controller.
KerberosKeytabFile The Keytab file containing your pairs of Kerberos principals and encrypted keys.
KerberosServiceRealm The Kerberos realm of the service.
KerberosServiceKDC The Kerberos KDC of the service.
KerberosTicketCache The full file path to an MIT Kerberos credential cache file.

KerberosKDC

The Kerberos Key Distribution Center (KDC) service used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:

  • KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
  • Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
  • Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the configured domain name and host.

Note

Windows authentication is supported in JRE 1.6 and above only.

KerberosRealm

The Kerberos Realm used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.

If Kerberos Realm is not specified, the connector will attempt to detect these properties automatically from the following locations:

  • KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
  • Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
  • Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the user-configured domain name and host. This might work in some Windows environments.

Note

Kerberos-based authentication is supported in JRE 1.6 and above only.

KerberosSPN

The service principal name (SPN) for the Kerberos Domain Controller.

Data Type

string

Default Value

""

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

KerberosKeytabFile

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Data Type

string

Default Value

""

Remarks

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

KerberosServiceRealm

The Kerberos realm of the service.

Data Type

string

Default Value

""

Remarks

The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosServiceKDC

The Kerberos KDC of the service.

Data Type

string

Default Value

""

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosTicketCache

The full file path to an MIT Kerberos credential cache file.

Data Type

string

Default Value

""

Remarks

This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\Exchange Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note

Given that this connector supports multiple schemas, the structure for Microsoft Exchange custom schema files is as follows:

  • Each schema is given a folder corresponding to that schema name.
  • These schema folders are contained in a parent folder.
  • The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\Exchange Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
AlwaysRequestTableDependencies Control whether you want to always retrieve Table Dependencies (Foreign Keys). Setting this to TRUE might slow down queries and increase amount of calls made.
BodyType The BodyType element identifies how the body text is formatted in the response. The possible options are: Best - The response will return the richest available content of body text, HTML - The response will return an item body as HTML, Text - The response will return an item body as plain text. The default is set to Best.
CustomHeaders Other headers as determined by the user (optional).
DirectoryRetrievalDepth Depth level of folder to query Folders and Items.
DisableServerSideFiltering Control whether you want to disable server-side filtering. By default this property is set to False, and server-side filtering is attempted for all columns/tables.
GroupId Specify this GroupId in order to access the OneNote documents for this group.
ImpersonationType The type of identifier to use with impersonation while sending requests to the Exchange site.
ImpersonationUser The user to impersonate while sending requests to the Exchange site.
IncludeContent A boolean indicating if additional content should be retrieved.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Microsoft Exchange.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

AlwaysRequestTableDependencies

Control whether you want to always retrieve Table Dependencies (Foreign Keys). Setting this to TRUE might slow down queries and increase amount of calls made.

Data Type

bool

Default Value

false

Remarks

Control whether you want to always retrieve Table Dependencies (Foreign Keys). Setting this to TRUE might slow down queries and increase amount of calls made.

BodyType

The BodyType element identifies how the body text is formatted in the response. The possible options are: Best - The response will return the richest available content of body text, HTML - The response will return an item body as HTML, Text - The response will return an item body as plain text. The default is set to Best.

Possible Values

Best, HTML, Text

Data Type

string

Default Value

Best

Remarks

The BodyType element identifies how the body text is formatted in the response. The possible options are: Best - The response will return the richest available content of body text, HTML - The response will return an item body as HTML, Text - The response will return an item body as plain text. The default is set to Best.

CustomHeaders

Other headers as determined by the user (optional).

Data Type

string

Default Value

""

Remarks

This property can be set to a string of headers to be appended to the HTTP request headers created from other properties, like ContentType, From, and so on.

The headers must be of the format "header: value" as described in the HTTP specifications. Header lines should be separated by the carriage return and line feed (CRLF) characters.

Use this property with caution. If this property contains invalid headers, HTTP requests may fail.

This property is useful for fine-tuning the functionality of the connector to integrate with specialized or nonstandard APIs.

DirectoryRetrievalDepth

Depth level of folder to query Folders and Items.

Data Type

string

Default Value

5

Remarks

This property must be set in the connection string or the driver uses a default of Depth=1.

DisableServerSideFiltering

Control whether you want to disable server-side filtering. By default this property is set to False, and server-side filtering is attempted for all columns/tables.

Data Type

bool

Default Value

false

Remarks

To be used in cases when complex queries are attempted and server-side filters are incapable or insufficient for producing the desired output.

GroupId

Specify this GroupId in order to access the OneNote documents for this group.

Data Type

string

Default Value

""

Remarks

Specify this GroupId in order to access the OneNote documents for this group.

ImpersonationType

The type of identifier to use with impersonation while sending requests to the Exchange site.

Possible Values

PrincipalName, SID, PrimarySmtpAddress, SmtpAddress

Data Type

string

Default Value

PrincipalName

Remarks

The type of identifier to use with impersonation while sending requests to the Exchange site. The pseudo-column of the same name can be used in the SQL query in order to more dynamically impersonate users.

Possible values for ImpersonationType are:

Property Description
PrincipalName Represents the user principal name (UPN) of the account to use for impersonation. This should be the UPN for the domain where the user account exists.
SID Represents the security descriptor definition language (SDDL) form of the security identifier (SID) for the account to use for impersonation.
PrimarySmtpAddress Represents the primary Simple Mail Transfer Protocol (SMTP) address of the account to use for Exchange impersonation. If the primary SMTP address is supplied, it will cost an extra Active Directory directory service lookup in order to obtain the SID of the user. We recommend that you use the SID or UPN if they are available.
SmtpAddress Represents the Simple Mail Transfer Protocol (SMTP) address of the account to use for Exchange Impersonation. If the SMTP address is supplied, it will cost an extra Active Directory lookup in order to obtain the SID of the user. We recommend that you use the SID or UPN if they are available.

ImpersonationUser

The user to impersonate while sending requests to the Exchange site.

Data Type

string

Default Value

""

Remarks

The user to impersonate while sending requests to the Exchange site. The pseudo column of the same name can be used in the SQL query in order to more dynamically impersonate users.

IncludeContent

A boolean indicating if additional content should be retrieved.

Data Type

bool

Default Value

false

Remarks

A boolean indicating if additional content should be retrieved, such as the Body field of an Inbox email. Retrieving all content for a large number of items can be expensive. This property cannot be used with the Folders table, or the Calendar child views.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Microsoft Exchange.

Data Type

int

Default Value

1000

Remarks

The Pagesize property affects the maximum number of results to return per page from Microsoft Exchange. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

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

Note that the specified path is not embedded in quotation marks.