Layer2 Data Provider for Exchange - Specifications

​The Layer2 Cloud Connector can be used to connect almost any external data source to Microsoft Exchange, on-premises or Exchange Online & Office 365. This page gives the full specifications of the Layer2 Data Provider for Exchange which is included in the Cloud Connector product starting with version 5.

​The Layer2 Data Provider for Exchange can connect and sync Microsoft Exchange contacts, tasks, calendar items, notes, and emails from private or shared mailboxes, and public folders, uni-directional and bi-directional with almost any other data source or system, including native SharePoint SharePoint lists and libraries - codeless. 

You simply have to:

• Start the Layer2 Cloud Connector Connection Manager to configure data source, data destination, field mapping, and sync options.
• Select the Layer2 Data Provider for Exchange.
• Enter a valid connection string with URL, user, password etc.
• Enter an Exchange data query like select * from contacts.
• Run first sync manually and configure automatic background sync if all works as expected.

You will find further specifications below.

Layer2 Data Provider for Microsoft Exchange: Connection String

​The connection is established directly to the Exchange Server, on-premises or in the cloud, not via Outlook. Exchange 2007 SP1, 2010, 2013, and Online is supported.

Sample Connection Strings:

Url=https://outlook.office365.com; [email protected]; NamingScheme=SharePoint; Password=myPassword

Url=https://myExchange; [email protected]; NamingScheme=SharePoint; Password=myPassword

Url=https://mail.mydomain.com; [email protected]; NamingScheme=SharePoint; Password=myPassword

[email protected]; [email protected]; NamingScheme=SharePoint; Password=myPassword

Url=https://192.100.100.1; [email protected];; NamingScheme=SharePoint; Password=myPassword

[Specifically for Site Mailbox Access]

[email protected]; Password=myPassword; email=userOfSiteMailBox; NamingScheme=SharePoint;

Specification

Url (required):

Link to the Exchange Web Services Interface. Please ask your administrator or use the autodiscover feature by giving an email adress only: [email protected]. Please note that SSL / HTTPS required appropiate certificates on the client system.

Username (required):

Email adress ([email protected]), user name (myUser) possibly including domain (myDomain\myUser). The service inpersonates to this user to get access to the requested folders or items. Please make sure that this user has read access minimum to retrieve data.

Password (required):

Password of the given user. Please note that the configuration file can be encrypted for security reasons and access to the directory can be limited by Windows file system access rights.

Authentication (optional):

In case user name and password is used instead of an email address, Authentication=Windows is required.

PreferredCulture (optional):

You can prefer a defined culture, e.g. en-US. If not given "Invariant" is used to make a best fit approach.

Possible values are typical Microsoft culture notations like de-de, de-ch etc.

NamingScheme (optional):

Optional parameter to perform optimizations for certain target systems. For example in case of NamingScheme=SharePoint the column names automatically fit to SharePoint contact, tasks or event lists.
Auto-mapping can be used to map the data source fields to the data destination columns by name. By default no optimization is done (means original field names are used). You can also provide some optimization in the query statement using aliases (see below).

Email (optional):

Defines which mailbox should be accessed. If not specified, it will use the mailbox of the user given under the Username parameter. If the Email parameter is specified, the user given in the Username parameter must have permissions to impersonate the mailbox account.  You may need to set Impersonation=true;

Impersonation (optional):

When using the "Email" parameter to access a different mailbox, you can either use impersonation (Impersonation=true;) or delegation (Impersonation=false;). This is set to false by default. 

Please verify the connection before continue with the data query.​

Recursive (optional):

Defines if the provider should traverse through all subfolders to gather the items (Recursive=True;) or only return items which are direct children of the specified folder (Recursive=False;). This is set to false by default.

Layer2 Data Provider for Microsoft Exchange: Data Query (SQL/AQS)

​The data query retrieves the desired data set from SharePoint. A SQL (Structured Query Language) like syntax is supported.

Sample data query (returns contacts):

select Title, FirstName, FullName, Email, Company,  JobTitle, WorkPhone, HomePhone, CellPhone, WorkFax, WorkAddress, WorkCity, WorkState, WorkZip, WorkCountry, Comments,  exchangeid from Contacts

Specification:

SELECT myFields FROM myScope WHERE MyCondition

myFields can be a list of fields like "Firstname, Fullname". * is supported for all fields. For performance reasons it is advised to use a list of fields. Please include required fields only. You can find out available field names using the data preview or the field mapping dialogue. You can use aliases to rename fields to better fit the target system, e.g. select Lastname as Title.

The amount of data can be limited with a TOP clause, e.g. SELECT TOP 500 * FROM myScope. By default the number of items is limited to 1000. Please use e.g. TOP 2000 to increase. Best to use "Top 10" for testing. 

myScope can be a valid path to any Exchange folder, e.g. Contacts. Please note that default folder names are localized (e.g. Kontakte in German). To access public folders use something like this:

Select * From PublicFoldersRoot\ExchangeTest\Kontakt

You can also access mailboxes from other users (if you have access) via

SELECT * from [email protected]/contacts.

You can use the full path to the folder, e.g. SELECT * from "Root\Top of Information Store\my contacts 1", for more complex folder names. Note that double quotes are necessary if your folder names contain spaces. 

MyCondition can be a valid expression in terms of AQS (Advanced Query Syntax). You will find more information here

https://msdn.microsoft.com/en-us/library/ee693615(v=exchg.150).aspx

and

here:

https://msdn.microsoft.com/en-us/library/office/dn579420(v=exchg.150).aspx

Samples:

SELECT * FROM contacts WHERE Body:progress

SELECT * FROM calendar WHERE category:customer

This first sample e.g. retrieves contacts only with the token "progress" in the notes / body field (not case sensitive). You can also search by categories as shown in the 2nd sample.

Please verify your query and display preview data before continue with mapping and synchronization.

Layer2 Data Provider for Microsoft Exchange: Primary Key(s)

No primary key must be given in Layer2 Cloud Connector. The Exchange Item-ID is set automatically. For the Layer2 Business Data List Connector please set the primary key to ID and add an additional text column to host the Exchange Item-ID. You can use bdlcID as column name to automatically map with ID. You can use any other name, e.g. ExchangeID to map manually to ID via custom mapping.

Cloud Connector and Business Data List Connector Sample Configurations

​An example configuration of a Microsoft Exchange data source in the Layer2 Cloud Connector can look like this:

Layer2 Data Provider for Exchange: Use Cases and Known Issues

Use Cases:

• Provide Microsoft Exchange data synced with a native SharePoint lists, e.g. contacts. Sync other item types as tasks, calendar data, notes.
• All list features supported, e.g. views, filter, grouping, search, calculated fields, lookups.
• Metadata and social tagging supported, as well as attachments.
• Change notifications and workflows on source data changes supported.
• Mobile access via SharePoint.
• Offline availability and sync with Outlook via SharePoint.
• Sync with almost any other corporate data sources: Virtually any data sources supported likeODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), RSS / XML feeds, SQL databases like MS SQL, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint (lists and libraries), local file system, cloud-based file stores, Exchange, Active Directory, Dynamics NAV/CRM, Navision, SAP and many more. More complex data sources, web services, custom COM or .NET objects, can be connected using 3rd party add-ons.

Known limitations and issues:

• Calendar items: All-day events and recurring events are currently not synchronized properly and are not fully supported. You can get around this issue with all-day events by using the Dynamic Column feature as outlined in this article.
• Contact items: Outlook aggregates contact information from several sources and can merge it for display. A contact synchronization manages information only as stored in Exchange Server (not in Outlook).
• Email / inbox items: Attachments are not supported.
• TimeZone fields are not supported.
• Contact items: Contact Groups/Distribution lists are currently not supported.
• Task items: Attachments are not supported. Dependencies are not supported.
• Binary blobs, such as images, are not supported.
• User Defined Fields (e.g. added in Outlook) are not supported.
• Depending on configuration and data amount, performance of first sync can be slow.
• Sub-fields like CompleteName.GivenName, CompleteName.SurName are read-only. In some cases, there are related fields available for write like GivenName, or SurName. Unfortunately for CompleteName.Title and CompleteName.Suffix no related fields are available at this time.
• Wildcards are not supported in folder scopes.
• Max item count is limited to 1000 items in Exchange by default. Please use an example like the following to increase it:
  Select Top 5000 * from Calendar
• Documents in public folders are not synced.
• Be careful with select *. In some cases, large attachments can slow down performance and end up in timeouts.
• The GAL cannot be synced using this provider.

Ready to go next steps?