Exchange (v2): Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
Line 50: Line 50:


=Mapping=
=Mapping=
==Move Email To A Different Folder==
Create a stage that INSERTS to the sp_MoveItem "table" (this is really calling a Stored Procedure inside the connector).  The table expects 3 things:
#ItemId - See below.
#ItemChangeKey - See below.
#FolderDisplayName - The name of the folder to move the email to.
==Retrieving Attachments==
==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.
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.

Revision as of 20:09, 16 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

Move Email To A Different Folder

Create a stage that INSERTS to the sp_MoveItem "table" (this is really calling a Stored Procedure inside the connector). The table expects 3 things:

  1. ItemId - See below.
  2. ItemChangeKey - See below.
  3. FolderDisplayName - The name of the folder to move the email to.

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 save returned JSON to an XREF.

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

Example Function to retrieve the ItemId.

Function ScriptedField
	dim json, itemid
	ParseJSON("{}")
	json = XrefRead("InforToExchangeIDs", "@@ORG:Key@@")
	ParseJSON json
	id = GetJSON("itemid")	
	ScriptedField=id
End Function

Example Function to retrieve the ItemChangeKey.

Function ScriptedField
	dim json, itemid, changeKey
	ParseJSON("{}")
	json = XrefRead("InforToExchangeIDs", "@@ORG:Key@@")
	ParseJSON json
	changeKey = GetJSON("changekey")
	If changeKey = "" And itemid <> "" Then
		id = GetJSON("itemid")
		ScriptedField = SmartLookup("Calendar","ItemChangeKey"," ItemId = '" & itemid & "'")
	End If
	ScriptedField=changeKey
End Function