- 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 email attachments from and load data into Microsoft Exchange.
If all you need is to read and send emails using Exchange as a mail server and 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.
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 areMSGraph
(default) andEWS
.Server
: this should be set to the Exchange Web Services URL. For Exchange Online, you should set it tohttps://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 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
. Example:
EXEC GetAttachment AttachmentIds='{AttachmentId}',
AttachmentFolder='{app.data}', Overwrite=false
Extract data from Exchange
If you are planning to extract email body and attachments, make sure that options Include Content
is f. 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
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}
. 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.
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.
Comments
0 comments
Please sign in to leave a comment.