Office 365 Inbound and Outbound connectors

• Startup
• Business
• Enterprise
• On-Premise
• Add-on

Use Inbound Email for Office 365 connector for reading emails and attachments from Office 365 (Exchange Online) using IMAP protocol. 

Use Outbound Email for Office 365 connector for sending emails and attachments from Office 365 (Exchange Online) accounts using SMTP protocol. 

Both connectors use OAuth2 for authentication. 

Microsoft Exchange premium connector

• Startup
• Business
• Enterprise
• On-Premise
• Add-on

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 messages, users, groups, events and email attachments from and load data into Microsoft Exchange.

If all you need is to read and send emails using Exchange Online as a mail server and the standard email protocols (IMAP and SMTP) are enabled for the Exchange account, then you can use Inbound Email for Office 365 connector for reading emails and Outbound Email for Office 365 connector for sending emails. Both connectors use OAuth2 for authentication. 

Prerequisites

Contact support@etlworks.com to enable Exchange premium connector for your account.

Create a Connection

Step 1. In the Connections window, click +, 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.

Work with Exchange

Exchange Data Models

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

• EWS Data Model (on-prem Exchange and Exchange online) 
• MSGraph Data Model (Office 365)

Stored procedures

Stored procedures are available to complement the data available from the Data Models. 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 EWS and MSGrpah data models.  

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

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

Extract data from Exchange

If you plan to extract the email body and attachments, ensure the option 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.

Extract attachments from Exchange using EWS data model (on-prem Exchange)

To extract attachments from the items in the inbox, you must use the GetAttachment stored procedure, 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}. Where 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) icon 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. 

Extract attachments from Exchange using MSGraph data model (Office 365)

To extract attachments from the items in the inbox, you must use the DownloadAttachments stored procedure, a part of the Exchange MSGraph 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 DownloadAttachments 
AttachmentSource='Message',
SourceId='{MessageId}', 
AttachmentId='{AttachmentId}', 
DownloadTo='{app.data}'

Note that {SourceId}, {AttachmentId} are tokenized and DownloadTo set to {app.data}. Where app.data points to the home folder in the local storage. You can add any subfolder to the app.data: {app.data}/attachments.

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

Step 6. Click the Edit condition (pen) icon 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. You must have an actual message id which you can get from messagetable.

Example:

select id as "AttachmentId", messageid as "MessageId"
from MessageAttachments
where id = 'actual message id'

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 email with the specific id

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

Load data in Exchange

Loading data in Exchange is similar to loading data into a 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.

Browse data in Exchange

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

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