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 theOAuthAccessToken
. 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:
- Extracts the access token from the callback URL and authenticates requests.
- Obtains a new access token when the old one expires.
- 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 theOAuthAccessToken
.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:
- Create a custom Azure AD application, as described in Creating a Custom OAuth Application.
- Assign a role to the application:
- Use the search bar to search for the Subscriptions service.
- Open the
Subscriptions
page. - Select the subscription to which to assign the application.
- Open the
Access control (IAM)
. - Select
Add > Add role assignment
. Microsoft Exchange opens theAdd role assignment
page. - Assign your custom Azure AD application the role of
Owner
.
Admin Consent¶
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 theOAuthAccessToken
.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
:
- Log in to
https://portal.azure.com
. - In the left-hand navigation pane, select
Azure Active Directory
, thenapplicationRegistrations
. - Click
New registration
. - Enter a name for the application.
- 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.
- If you select the default option, "Accounts in this organizational directory only", you must set the
- Set the redirect URL to
http://localhost:33333
(the connector's default) OR specify a different port and setCallbackURL
to the exact reply URL you defined. - Click
Register
to register the new application. An application management screen displays.
Note the value inApplication (client) ID
as theOAuthClientId
and theDirectory (tenant) ID
as theAzureTenant
. - 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
, selectUpload certificate
, then upload the certificate from your local machine. - For creating a new client secret: In
Certificates & Secrets
, selectNew 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 theOAuthClientSecret
.
- For certificate authentication: In
- Select
API Permissions > Add > Delegated permissions
. - 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.
- Save your changes.
- 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
:
- Create a custom OAuth AD application, as described above.
- Use the search bar to search for the Subscriptions service.
- Open the
Subscriptions
page. - Select the subscription to which to assign the application.
- Open the
Access control (IAM)
. - Select
Add > Add role assignment
. Microsoft Exchange opens theAdd role assignment
page. - Assign your custom Azure AD application the role of
Owner
.
Admin Consent¶
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:
- Have an admin log in to
portal.azure.com
. - Navigate to
App Registrations
and find the custom OAuth application you created. - Under
API Permissions
, clickGrant Consent
.
This gives your application permissions on the tenant under which it was created.
Consent for Client Credentials¶
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
:
- Create a custom OAuth application, as described above.
- Navigate to
App Registrations
. - Find the application you just created, and open
API Permissions
. - Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
- 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:
- Ensure that the
KRB5CCNAME
variable is present in your environment. - 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. -
To obtain a ticket:
- Open the MIT Kerberos Ticket Manager application.
- Click
Get Ticket
. - Enter your principal name and password.
- 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:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
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.
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. |
Property | Description |
---|---|
AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
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. |
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. |
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. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
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. |
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. SetAuthScheme
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:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.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.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 propertiesjava.security.krb5.realm
andjava.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 propertiesjava.security.krb5.realm
andjava.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 theLocation
, 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.