Microsoft Exchange connector

About Microsoft Exchange connector

Etlworks Microsoft Exchange connector allows fast, real-time access to Exchange API. The connector works just like any other database connector.

Etlworks partnered with CData to provide access to the Microsoft Exchange using industry-standard JDBC protocol. Read about CData Exchange JDBC connector.

When to use Microsoft Exchange connector

Use this connector to create flows that extract data, including email attachments from and load data into Microsoft Exchange.

If all you need is read and send emails using Exchange as a mail server AND (important) the standard email protocols (IMAP/PO3 and SMTP) are enabled for the Exchange account, then you can use built-in email connectors.

Prerequisites

Enable Exchange connector for your Etlworks account. Contact support@etlworks.com to enable the connector.

Creating a connection

Step 1. In the Connections window click the+button and type in exchange

Step 2. Enter connection parameters

• Schema - the Exchange schema. The available schemas are MSGraph (default) and EWS.
• Server - 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.
• Platform - the Platform associated with the Exchange server.

When using MSGraph (OAuth2) authentication (default):

• Permissions - select the permissions that will be used to access the Exchange account. 
• OAuth Token - sign in with Microsoft

When using EWS authentication (on-premise Exchange only):

• User - the user name
• Password - the password

Use Other parameters to specify the connection string options. Read about available connection string options.

Working with Exchange

Exchange Data Models

Our connector for Exchange 2019 models the Exchange 2 and Exchange APIs as relational tables, views, and stored procedures.

Read about Exchange data models.

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.

Read about available stored procedures in the data model.  

To call stored procure from the SQL flow or from Before/After SQL use EXEC sp_name params=value syntax. Example:

EXEC GetAttachment AttachmentIds='{AttachmentId}', 
AttachmentFolder='{app.data}', Overwrite=false

Extracting data from Exchange

If you are planning to extract email body and attachments, make sure that options Include Content is enabled. It is enabled by default.

Extracting data from Exchange is similar to extracting data from the relational database.

Step 1. Create Exchange connection which will be used as a source (FROM).

Step 2. Create a destination connection, for example, a connection to the relational database, and if needed, a format (format is not needed if the destination is a database or well-known API).

Step 3. Create a flow where the source is a database and the destination is a connection created in step 2, for example, a relational database.

Step 4. Add a new source-to-destination transformation.

Step 5. Select the Exchange connection created in step 1 as a source connection and select the data object you are extracting data from.

Step 6. Select TO connection, format (if needed) and object (for example database table) to load data into.

Step 7. Click MAPPING and optionally enter Source Query (you don't need a query if you are extracting data from the Exchange data object unconditionally).

Step 8. Optionally define the per-field mapping.

Step 9. Add more transformations if needed.

Extracting attachments from Exchange

To extract attachments from the items in the inbox you must use GetAttachment stored procedure, which is a part of the Exchange EWS data model.

Step 1. Create Exchange connection.

Step 2. Create SQL flow. Set the connection to the connection created in step 1.

Step 3. Set SQL under the Parameters tab to:

EXEC GetAttachment AttachmentIds='{AttachmentId}', 
AttachmentFolder='{app.data}', Overwrite=false

Note that {AttachmentId} is tokenized and AttachmentFolder set to {app.data}. app.data points to the home folder in the local storage. You can add any subfolder to the app.data: {app.data}/attachments

The Override parameter is set to false which means that conflicting files (attachments) will be indexed. The other (default) option is true, telling the system to override conflicting files. 

Step 5. Create nested flow and add flow created in steps 2-3 as a first step.

Step 6. Click the Edit condition (pen) button next to the flow name and change Loop type to SQL. Set Connection to the Exchange connection created in step 1. Enter the SQL in the Loop script field which will return the IDs of the attachments to extract.

Example:

select AttachmentId
from inbox
where AttachmentId is not null 
and subject LIKE '%Email to test attachments feature from exchange%'

Step 7. Save the flow.

When this flow is executed it will save all the attachments [into the local storage folder] in the inbox for the emails with the subject which matches the wildcard line 'Email to test attachments feature from exchange'. 

You can later add other steps to the nested flow to, for example, further process the attachments. 

Loading data in Exchange

Loading data in Exchange is similar to loading data into relational database.

Step 1. Create a source connection and a format (if needed).

Step 2. Create a destination Exchange connection.

Step 3. Create a flow where the destination is a database.

Step 4. Add new source-to-destination transformation.

Step 5. Select FROM and TO connections and objects (also a FROM format if needed).

Step 6. Optionally define the per-field mapping.

Step 7. Add more transformations if needed.

Browsing data in Exchange

You must have an Exchange connection to browse objects and run SQL queries.

Use Explorer to browse data and metadata in Exchange as well as execute DML and SELECT queries against the Exchange connection.