Exchange (v2): Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
Line 58: Line 58:
</syntaxhighlight>
</syntaxhighlight>
Attachment Names are also stored in an array: @@ORG:AttachmentName@@.  You will need to process this array to get the attachment names.
Attachment Names are also stored in an array: @@ORG:AttachmentName@@.  You will need to process this array to get the attachment names.
==Update/Insert Stages==
In order to run Update/Insert Stages you MUST do a few things.
*On the Stage Edit screen, you MUST check the "Omit Blank Field Value Writes" checkbox.
*When Mapping, you MUST map and Match on both the ItemId and ItemChangeKey Destination Fields.
*Every time you Update a record, the ItemChangeKey changes.  As such, you must record the new value to your local XREF or back into the Origin system.
*The GetStageValue(0, "#ID") code returns JSON and must be parsed to get both the ItemId and ItemChangeKey for use on Updates.
Example Function to write the JSON to an XREF.
<script lang="vb">
Sub VBScriptProcedure
dim json
json = GetStageValue(0, "#ID")
If CurrentStageName = "Upsert Event" And PreviewMode = False And "@@STG:0,#Action@@" <> "Error" Then
XrefWrite "InforToExchangeIDs", "@@ORG:Key@@", json
End If
End Sub
</script>

Revision as of 05:49, 8 March 2018

Overview

The "Exchange (NEW)" connector is based on a driver from https://www.cdata.com/. Helpful documentation for using the "Exchange (NEW)" connector can be found here: http://cdn.cdata.com/help/CEC/ado/pg_alltables.htm.

Origin

Web Services URL

If you are using Office 365 for your Exchange server, then your Web Service URL is https://mail.office365.com/ews/exchange.asmx. If you are hosting Exchange yourself or using another cloud provider, then your URL will look slightly different but will still likely end with exhcange.asmx.

SQL Selection Statement

Use the Query Builder... hyperlink on the right to build your initial queries. Check out the CData documentation select page for more information about writing queries, http://cdn.cdata.com/help/CEC/ado/pg_select.htm.

Destination

Web Service URL

If you are using Office 365 for your Exchange server, then your Web Service URL is https://mail.office365.com/ews/exchange.asmx. If you are hosting Exchange yourself or using another cloud provider, then your URL will look slightly different but will still likely end with exhcange.asmx.

Platform

Choose your version of Exchange.

Authentication Scheme

Choose your authentication scheme. Office 365 seems to use BASIC. The only other Authentication Scheme I've seen used is NTLM. You may have to trial and error this option.

Impersonation

Impersonation User

Enter the email address of the user you would like to impersonate. Note that you can put a variable in this field, so you can use a script to loop through many destination accounts.

Impersonation Type

Choose your Impersonation Type.

Additional Connection String Parameters

See the Connection String Options for more information about what can go here, http://cdn.cdata.com/help/CEC/ado/Connection.htm.

Example:
Include Content=true;Other="BodyType=Text";

Here are a couple of items:

Parameter Description
Include Content=true; A boolean indicating if additional content should be retrieved. Addiditional content includes the body of emails.
Other="BodyType=Text"; OR Other="BodyType=HTML"; Returns the body value in either Text or HTML format.
Logfile=C:\inetpub\wwwroot\StarfishEngine\exchange.log; Enable logging and set a path to the log file.
Verbosity=x; x=1-5. The verbosity level that determines the amount of detail included in the log file. See http://cdn.cdata.com/help/CEC/ado/RSBExchange_p_Verbosity.htm for what each option logs.

Mapping

Retrieving Attachments

To retrieve attachments from an email, you must perform a SmartLookup and pass in the attachment's ID. Note that the origin field, @@ORG:AttachmentId@@, will sometimes contain an array of IDs, so you must always do a Split on the Attachment Id (comma-seperated list) and loop through for each id. The SmartLookup will return the attachment as a Base64 string.

Sub VBScriptProcedure
    Logmessage SmartLookup("GetAttachment", "Content", "@@ORG:AttachmentId@@")
End Sub

Attachment Names are also stored in an array: @@ORG:AttachmentName@@. You will need to process this array to get the attachment names.

Update/Insert Stages

In order to run Update/Insert Stages you MUST do a few things.

  • On the Stage Edit screen, you MUST check the "Omit Blank Field Value Writes" checkbox.
  • When Mapping, you MUST map and Match on both the ItemId and ItemChangeKey Destination Fields.
  • Every time you Update a record, the ItemChangeKey changes. As such, you must record the new value to your local XREF or back into the Origin system.
  • The GetStageValue(0, "#ID") code returns JSON and must be parsed to get both the ItemId and ItemChangeKey for use on Updates.

Example Function to write the JSON to an XREF. <script lang="vb"> Sub VBScriptProcedure dim json json = GetStageValue(0, "#ID") If CurrentStageName = "Upsert Event" And PreviewMode = False And "@@STG:0,#Action@@" <> "Error" Then XrefWrite "InforToExchangeIDs", "@@ORG:Key@@", json End If End Sub </script>