Microsoft Exchange Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 21.0.8662 of the connector.
Important
We recommend using the Microsoft Exchange v2 connector to connect to Microsoft Exchange as it allows for additional configuration properties.
Get Started¶
Exchange Version Support
The Jitterbit Connector for Exchange connects with the REST API.
Establish a Connection¶
Available Schemas¶
There are two services available for connecting to Exchange. These are EWS (Exchange Web Services) and the Microsoft Graph. Exchange Web Services is available for both Exchange OnPremise and Online, but is no longer receiving updates. Microsoft recommends switching to using the Microsoft Graph for Exchange Online users. Both are available with our tool.
To switch between the two, use the Schema
connection property to set either EWS or MSGraph. If you wish to use EWS with Exchange Online, set Schema
to EWS and the Platform
to Exchange_Online.
Connect to Exchange using Exchange OnPremise¶
When using an OnPremise edition of Exchange, OnPremise Set User
, Password
, and AuthScheme
; by default, the connector performs Basic authentication, but Windows (NTLM), Kerberos, and delegated authentication are also supported.
Authenticate with Kerberos¶
Please see Using Kerberos for details on how to authenticate with Kerberos
In addition to the authentication values, set the Server
property to the address of the Exchange server you are connecting to and set Platform
to the Exchange version. Finally, set Schema
to EWS.
Connect to Exchange using Exchange Online¶
When connecting to Exchange Online, authentication will be done via OAuth. If you are connecting to Exchange Online platform through EWS, set the AuthScheme
property to either AzureAD, AzureServicePrincipal or AzureMSI. Otherwise if you will be using Microsoft Graph to connect to Exchange Online, resources will be pulled from a different service so the Schema
should be set to MSGraph. When Schema
is set to MSGraph, the Platform
value will be ignored.
Authenticate using Azure AD¶
Azure AD is a connection type that goes through OAuth. Set your AuthScheme
to AzureAD
and see Using OAuth Authentication for an authentication guide.
Authenticate using Azure Service Principal¶
Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme
to AzureServicePrincipal
and see Using Azure Service Principal Authentication for an authentication guide.
Authenticate using MSI Authentication¶
If you are running Exchange on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:
AuthScheme
: Set this toAzureMSI
.
The MSI credentials will then be automatically obtained for authentication.
Use OAuth Authentication¶
OAuth requires the authenticating user to interact with Exchange using the browser. The connector facilitates this in various ways as described below.
Embedded Credentials¶
See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.
Custom Credentials¶
Instead of connecting with the connector's embedded credentials, you can register an app with Custom Credentials to obtain the OAuthClientId
and OAuthClientSecret
.
When to Create a Custom OAuth App¶
Creating a custom OAuth app is optional as the connector is already registered with Exchange and you can connect with its embedded credentials. You might want to create a custom OAuth app to change the information displayed when users log into the Exchange OAuth endpoint to grant permissions to the connector.
Create a Custom OAuth App¶
See Creating a Custom OAuth App for a procedure.
Embedded Credentials¶
Authenticate using the Embedded OAuth Credentials¶
Desktop Authentication with the Embedded OAuth App¶
You can connect without setting any connection properties for your user credentials.
When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.
- 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
to be persisted across connections.
Custom Credentials¶
There are two types of app authentication available: using a client secret and using a certificate. You can use any of them depending on the configured app authentication.
Desktop Authentication with Your OAuth App¶
Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.
Get an OAuth Access Token¶
You are ready to connect after setting one of the below connection properties groups depending on the authentication type.
Authenticating using a Client Secret
OAuthClientId
: Set this to the Client ID in your app settings.OAuthClientSecret
: Set this to the Client Secret in your app settings.CallbackURL
: Set this to the Redirect URL in your app settings.AuthScheme
: Set this to the "AzureAD" in your app settings.InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
. .
Authenticating using a Certificate
OAuthClientId
: Set this to the Client ID in your app settings.OAuthJWTCert
: Set this to the JWT Certificate store.OAuthJWTCertType
: Set this to the type of the certificate store specified byOAuthJWTCert
.CallbackURL
: Set this to the Redirect URL in your app settings.AuthScheme
: Set this to the "AzureAD" in your app settings.InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
. .
When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:
- 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
to be persisted across connections.
Headless Machines¶
Use OAuth on a Headless Machine¶
To create Exchange data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.
- Instead of installing the connector on another machine, you can follow the steps below to obtain the
OAuthVerifier
value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow. - You can then configure the connector to automatically refresh the access token from the headless machine.
You can follow the headless OAuth authentication flow using the connector's embedded OAuth credentials or using the OAuth credentials for your custom OAuth app.
Use the Credentials for a Custom OAuth App¶
Create a Custom OAuth App
See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.
Obtain a Verifier Code
On the headless machine, set one the following properties groups depending on the authentication type:
Authenticating using a Client Secret
InitiateOAuth
: Set this to OFF.OAuthClientId
: Set this to the App ID in your app settings.OAuthClientSecret
: Set this to the App Secret in your app settings.
Authenticating using a Certificate
InitiateOAuth
: Set this to OFF.OAuthClientId
: Set this to the App ID in your app settings.OAuthJWTCert
: Set this to the JWT Certificate store.OAuthJWTCertType
: Set this to the type of the certificate store specified byOAuthJWTCert
.
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier
connection property.
- Call the GetOAuthAuthorizationURL stored procedure with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
- Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL. The webpage will state that the site could not be reached.
- Inspect the URL of the this site page and find the
code
value. It will be present in the URL in the form code=XXXXXX (terminated with &, which denotes the next URL parameter) The value after the "code=" is the verifier code. - Save the value of the verifier code. You will set this in the
OAuthVerifier
connection property.
On the headless machine, set the one of the following connection properties groups depending on the authentication type to obtain the OAuth authentication values:
OAuthClientId
: Set this to the consumer key in your app settings.OAuthClientSecret
: Set this to the consumer secret in your app settings.OAuthVerifier
: Set this to the verifier code.OAuthSettingsLocation
: Set this to persist the encrypted OAuth authentication values to the specified file.InitiateOAuth
: Set this to REFRESH.
Connect to Data
After the OAuth settings file is generated, set the following properties to connect to data:
OAuthSettingsLocation
: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.InitiateOAuth
: Set this to REFRESH.
Transfer OAuth Settings
Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the connector and connect with the one of the following properties groups depending on the authentication type:
Authenticating using a Client Secret
OAuthSettingsLocation
: Set this to a writable text file.InitiateOAuth
: Set this to GETANDREFRESH.OAuthClientId
: Set this to the Client ID in your app settings.OAuthClientSecret
: Set this to the Client Secret in your app settings.CallbackURL
: Set this to the Callback URL in your app settings.
Authenticating using a Certificate
OAuthSettingsLocation
: Set this to a writable text file.InitiateOAuth
: Set this to GETANDREFRESH.OAuthClientId
: Set this to the Client ID in your app settings.OAuthJWTCert
: Set this to the JWT Certificate store.OAuthJWTCertType
: Set this to the type of the certificate store specified byOAuthJWTCert
.CallbackURL
: Set this to the Callback URL in your app settings.
Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation
. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
InitiateOAuth
: Set this to REFRESH.OAuthSettingsLocation
: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.
Create a Custom OAuth App¶
When to Create a Custom OAuth App¶
Creating a custom OAuth app is optional as the connector is already registered with Exchange and you can connect with its embedded credentials.
You might want to create a custom OAuth app to change the information displayed when users log into the Exchange OAuth endpoint to grant permissions to the connector.
Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.
Steps to Create a Custom OAuth App¶
Follow the steps below to obtain the OAuth values for your app, the OAuthClientId
and OAuthClientSecret
.
-
Log in to
https://portal.azure.com
. -
In the left-hand navigation pane, select Azure Active Directory then App Registrations and click the New registration button.
-
Enter an app name and set the radio button for the desired tenant setup.
When creating a custom OAuth application in Azure Active Directory, you can define if the application is single- or multi-tenant. If you select the default option of "Accounts in this organizational directory only", you will need to set the
AzureTenant
connection property to the ID of the Azure AD Tenant when establishing a connection with the Jitterbit Connector for Exchange. Otherwise, the authentication attempt will fail with an error. If your app is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your app, choose one of the multi-tenant options. -
Then set the redirect URL to something such as
http://localhost:33333
, the connector's default. Or, set a different port of your choice and setCallbackURL
to the exact reply URL you defined. -
Define the app authentication type by going to the Certificates & Secrets section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate, but you can also create an application secret.
Option 1 - Upload a certificate
: In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.Option 2 - Create a new application secret
: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as theOAuthClientSecret
.
-
Select API Permissions and then click Add. If you plan for your app to connect without a user context, select the Application Permissions (
OAuthGrantType
= CLIENT). Otherwise, when selecting permissions, use the Delegated permissions. -
If you are connecting to Exchange through EWS schema, select Exchange API and add EWS.AccessAsUser.All permission. If you are connecting to 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 selected to use permissions that require admin consent (such as the Application Permissions), you may grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under Admin Consent.
Admin Consent¶
Admin consent refers to when the Admin for an Azure Active Directory tenant grants permissions to an application which requires an admin to consent to the use case. The embedded app within the Jitterbit Connector for Exchange, contains no permissions that require admin consent. Therefore, this information applies only to custom applications.
Admin Consent Permissions¶
When creating a new OAuth app in the Azure Portal, you must specify which permissions the app will require. Some permissions may be marked stating "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your app requires admin consent, there are a couple of ways this can be done.
The easiest way to grant admin consent is to just have an admin log into portal.azure.com and navigate to the app you have created in App Registrations. Under API Permissions, there will be a button for Grant Consent. You can consent here for your app to have permissions on the tenant it was created under.
If your organization has multiple tenants or the app needs to be granted permissions for other tenants outside your organization, the GetAdminConsentURL may be used to generate the Admin Authorization URL. Unlike the GetOAuthAuthorizationURL, there will be no important information returned from this endpoint. If the admin grants access, it will simply return a boolean indicating that permissions were granted.
Once an admin grants consent, authentication may be performed as normal.
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 app itself. All tasks taken by the app are done without a default user context. This makes the authentication flow a bit different from standard.
Client OAuth Flow¶
All permissions related to the client oauth flow require admin consent. This means the app embedded with the Jitterbit Connector for Exchange cannot be used in the client oauth flow. You must create your own OAuth app in order to use client credentials. See Creating a Custom OAuth App for more details.
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 and Application permissions. The permissions used during client credential authentication are under Application Permissions. Select the applicable permissions you require for your integration.
You are ready to connect after setting one of the below connection properties groups depending on the authentication type.
Authenticating using a Client Secret
InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.AzureTenant
: Set this to the tenant you wish to connect to.OAuthGrantType
: Set this to CLIENT.OAuthClientId
: Set this to the Client ID in your app settings.OAuthClientSecret
: Set this to the Client Secret in your app settings.
Authenticating using a Certificate
InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.AzureTenant
: Set this to the tenant you wish to connect to.OAuthGrantType
: Set this to CLIENT.OAuthClientId
: Set this to the Client ID in your app settings.OAuthJWTCert
: Set this to the JWT Certificate store.OAuthJWTCertType
: Set this to the type of the certificate store specified byOAuthJWTCert
.
Authentication with client credentials will take place automatically like any other connection, except there will be 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.
Use Azure Service Principal Authentication¶
The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow, and it does not involve direct user authentication. Instead, credentials are created for just the app itself. 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.
Custom Credentials¶
You will need to register an OAuth app to obtain the OAuth property values before connection to the Exchange data source. You can check the Custom Credentials guide on how to set the OAuth properties.
Create a Custom OAuth App¶
See Creating a Custom OAuth App for a procedure.
Create a Custom OAuth App¶
Creating a custom OAuth app and a service principal that can access the necessary resources is required when authenticating using an Azure Service Principal.
Follow the steps below to create a custom OAuth app and obtain the connection properties for the Azure Service Principal authentication.
Steps to Create a Custom OAuth App¶
Follow the steps below to obtain the OAuth values for your app.
- Log in to
https://portal.azure.com
. - In the left-hand navigation pane, select Azure Active Directory then App Registrations and click on New registration button.
- Enter an app name and set the radio button for "Any Azure AD Directory - Multi Tenant". Then set the redirect URL to something such as
http://localhost:33333
, the connector's default. - Copy the Application (client) ID value displayed on the Overview section after creating the app, since this value is used as the
OAuthClientId
- Define the app authentication type by going to the Certificates & Secrets section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate, but you can also create an application secret.
Option 1 - Upload a certificate
: In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.Option 2 - Create a new application secret
: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as theOAuthClientSecret
.
- In the Authentication tab, make sure to check the option: Access tokens (used for implicit flows).
- Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
- Select the particular subscription to assign the application to, then open the Access control (IAM) section, and click on the Add role assignment button.
- Select Owner as the role to assign to your created OAuth app.
Custom Credentials¶
Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.
Authentication with Your OAuth App¶
There are two types of app authentication available: using a client secret and using a certificate. You can use any of them depending on the configured app authentication.
Get an OAuth Access Token¶
You are ready to connect after setting one of the below connection properties groups depending on the authentication type.
Authenticating using a Client Secret
AuthScheme
: Set this to the "AzureServicePrincipal" in your app settings.InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.AzureTenant
: Set this to the tenant you wish to connect to.OAuthClientId
: Set this to the Client ID in your app settings.OAuthClientSecret
: Set this to the Client Secret in your app settings.
Authenticating using a Certificate
AuthScheme
: Set this to the "AzureServicePrincipal" in your app settings.InitiateOAuth
: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting theOAuthAccessToken
.AzureTenant
: Set this to the tenant you wish to connect to.OAuthClientId
: Set this to the Client ID in your app settings.OAuthJWTCert
: Set this to the JWT Certificate store.OAuthJWTCertType
: Set this to the type of the certificate store specified byOAuthJWTCert
.
Use Kerberos¶
This section shows how to use the connector to authenticate to Exchange using Kerberos.
Authenticate with Kerberos¶
To authenticate to Exchange using Kerberos, set the following properties:
AuthScheme
: Set this toNEGOTIATE
.KerberosKDC
: Set this to thehost name or IP Address
of your Kerberos KDC machine.KerberosRealm
: Set this tothe realm of the Exchange Kerberos principal
. This will be the value after the '@' symbol (for instance, EXAMPLE.COM) of theprincipal value
(for instance, ServiceName/MyHost@EXAMPLE.COM).KerberosSPN
: Set this to theservice and host of the Exchange Kerberos Principal
. This will be the value prior to the '@' symbol (for instance, ServiceName/MyHost) of theprincipal value
(for instance, ServiceName/MyHost@EXAMPLE.COM).
Retrieve the Kerberos Ticket¶
You can use one of the following options to retrieve the required Kerberos ticket.
MIT Kerberos Credential Cache File¶
This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you won't need to set the User
or Password
connection properties with this option.
- Ensure that you have an environment variable created called
KRB5CCNAME
. - Set the
KRB5CCNAME
environment variable to a path pointing to yourcredential cache file
(for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file will be created when generating your ticket with MIT Kerberos Ticket Manager. - To obtain a ticket, open the MIT Kerberos Ticket Manager application, click
Get Ticket, enter your principal name and password, then click OK
. If successful, ticket information will appear in Kerberos Ticket Manager and will now be stored in the credential cache file. - Now that the credential cache file has been created, the connector will use the cache file to obtain the kerberos ticket to connect to Exchange.
As an alternative to setting the KRB5CCNAME
environment variable, you can directly set the file path using the KerberosTicketCache
property. When set, the connector will use the specified cache file to obtain the kerberos ticket to connect to Exchange.
Keytab File¶
If the KRB5CCNAME environment variable has not been set
, you can retrieve a Kerberos ticket using a Keytab File
. To do this, 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 both the KRB5CCNAME environment variable and the _KerberosKeytabFile_ property have not been set
, you can retrieve a ticket using a User and Password combination
. To do this, set the User
and Password
properties to the user/password combo that you use to authenticate with Exchange.
Cross-Realm Authentication¶
More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g. where one realm/KDC is used for user authentication and another realm/KDC used for obtaining the service ticket).
In such an environment, the KerberosRealm
and KerberosKDC
properties can be set to the values required for user authentication. The KerberosServiceRealm
and KerberosServiceKDC
properties can be set to the values required to obtain the service ticket.
Exchange Online Administrative Tasks¶
The Jitterbit Connector for 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 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.
User Defined Views¶
The Jitterbit Connector for 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"
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 Exchange models the Exchange 2 and 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. You can use the SupportEnhancedSQL
feature, set by default, to circumvent most of these limitations.
Overview
The Data Models illustrate an example of what your Exchange environment might look like. The actual data model will be obtained dynamically based on your Exchange account.
Key Features
- Tables and Views are dynamically defined to model calendars, documents, and projects on Exchange.
- Stored procedures allow you to execute operations to Exchange, including downloading and uploading objects.
- Live connectivity to these objects means any changes to your Exchange account are immediately reflected when using the connector.
EWS Data Model
EWS Data Model describes the schemas available to connect to Exchange OnPremise and Exchange Online using EWS. You can use tables to work with live Exchange data. You can use stored procedures provided by Jitterbit Connector for Exchange to automate working with Exchange data.
MSGraph Data Model
MSGraph Data Model describes the schemas available to connect to Exchange Online accounts via the Microsoft Graph. You can use tables to work with live Exchange data. You can use stored procedures provided by Jitterbit Connector for Exchange to automate working with Exchange data.
EWS Data Model¶
The Jitterbit Connector for Exchange models 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 Exchange. They can be used to search, update, and modify information in Exchange.
Tables¶
The connector models the data in Exchange into a list of tables that can be queried using standard SQL statements.
Generally, querying Exchange tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Jitterbit Connector for 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). If you wish to view the individual recurrences of a recurring event, you'll need to specify the ItemId . 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'
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 | 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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. This property is read-only. |
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). |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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). |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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). |
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. |
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). |
ParentFolderName | String | Represents the distinguished folder ID of the parent folder. |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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_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 inbox items of a shared mailbox. |
JunkEmail¶
Create, update, delete, and query Junk Email items.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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). |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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). |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. |
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). |
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 |
---|---|---|---|
MimeContent | String | True | Contains the native Multipurpose Internet Mail Extensions (MIME) stream of an object that is represented in Base64 binary format. |
ItemId [KEY] | String | True | Contains the unique identifier of an item in the Exchange store. This property is read-only. |
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. |
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_NumberOfOccurences | 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 | 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). |
Stored Procedures¶
Stored procedures are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Jitterbit Connector for 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 Exchange', Content = 'I am not being able to connect to Exchange', Attachments = 'C:\Users\User\Desktop\logfile.txt,C:\Users\User\Desktop\TestConnectionLog.txt', ToRecipients = 'example@jbexample.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'. |
MSGraph Data Model¶
The Jitterbit Connector for Exchange models Exchange objects as relational tables and views. A 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 Exchange APIs.
Schemas for most database objects are defined in simple, text-based configuration files.
The connector offloads as much of the SELECT statement processing as possible to the Exchange APIs and then processes the rest of the query in memory. See SupportEnhancedSQL
for more information on how the connector circumvents API limitations with in-memory client-side processing.
Tables¶
The connector models the data in Exchange into a list of tables that can be queried using standard SQL statements.
Generally, querying Exchange tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Jitterbit Connector for 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
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
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 composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
Jitterbit Connector for 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. |
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 will be returned. You can filter results by CalendarId, Start_DateTime, End_DateTime.
For example the following queries will be 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 will remain 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 |
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 available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Jitterbit Connector for Exchange Stored Procedures¶
Name | Description |
---|---|
AddAttachments | Add attachment(s) to a message. |
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. |
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 | False | 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. |
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. |
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. |
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. |
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. |
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 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. |
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_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.
Collaborative Query Processing¶
When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. 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 |
---|---|
AuthScheme | The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate and OAuth. |
Schema | Specify the Exchange schema. |
Platform | The Platform associated with the Exchange server. |
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 tentant will be 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. |
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 |
---|---|
KerberosKDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
KerberosRealm | The Kerberos Realm used to authenticate the user with. |
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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from 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. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
AuthScheme | The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate and OAuth. |
Schema | Specify the Exchange schema. |
Platform | The Platform associated with the Exchange server. |
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. |
AuthScheme¶
The scheme used for authentication. Accepted entries are NTLM, Basic, Digest, None, Negotiate and OAuth.
Possible Values¶
NTLM
, Basic
, Digest
, NONE
, Negotiate
, OAuth
, AzureAD
, AzureServicePrincipal
, AzureMSI
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.
Schema¶
Specify the Exchange schema.
Possible Values¶
EWS
, MSGraph
Data Type¶
string
Default Value¶
EWS
Remarks¶
The schemas available are EWS and MSGraph.
Platform¶
The Platform associated with the Exchange server.
Possible Values¶
Exchange2007
, Exchange2007_SP1
, Exchange2010
, Exchange2010_SP1
, Exchange2010_SP2
, Exchange2013
, Exchange2013_SP1
, Exchange_Online
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. Instead, the connector parses the WHERE clause in memory.
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 tentant will be used. |
AzureTenant¶
The Microsoft Online tenant being used to access data. If not specified, your default tentant will be 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. |
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 grant type for the OAuth flow. 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 file. Set InitiateOAuth to GETANDREFRESH 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.
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 with. |
KerberosSPN | The service principal name (SPN) for the Kerberos Domain Controller. |
KerberosKeytabFile | The Keytab file containing your pairs of Kerberos principals and encrypted keys. |
KerberosServiceRealm | The Kerberos realm of the service. |
KerberosServiceKDC | The Kerberos KDC of the service. |
KerberosTicketCache | The full file path to an MIT Kerberos credential cache file. |
KerberosKDC¶
The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.
If Kerberos KDC is not specified, the connector will attempt to detect these properties automatically from the following locations:
KRB5 Config File (krb5.ini/krb5.conf)
: If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the KDC from the specified file. Otherwise it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)Java System Properties
: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.Domain Name and Host
: The connector will infer the Kerberos Realm and Kerberos KDC from the configured domain name and host as a last resort.
Note
Windows authentication is supported in JRE 1.6 and above only.
KerberosRealm¶
The Kerberos Realm used to authenticate the user with.
Data Type¶
string
Default Value¶
""
Remarks¶
The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.
If Kerberos Realm is not specified the connector will attempt to detect these properties automatically from the following locations:
KRB5 Config File (krb5.ini/krb5.conf)
: If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the default realm from the specified file. Otherwise it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)Java System Properties
: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.Domain Name and Host
: The connector will infer the Kerberos Realm and Kerberos KDC from the user-configured domain name and host as a last resort. 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 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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from 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. |
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 will use 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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
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 Exchange.
Data Type¶
int
Default Value¶
1000
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from 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.