Exchange (v2): Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
No edit summary
 
(29 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Overview=
=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.
The "Exchange (NEW)" connector is based on a driver from https://www.cdata.com/.  Helpful documentation for using the "Exchange (v2)" connector can be found here: http://cdn.cdata.com/help/CEC/ado/pg_alltables.htm.


=Origin=
=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==
==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.
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.


==Additional Connection String Parameters==
==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.   
See the Connection String Options for more information about what can go here, http://cdn.cdata.com/help/CEC/ado/Connection.htm.   
Example:<br />
Include Content=true;Other="BodyType=Text";


Here are a couple of items:
Here are a couple of items:
Line 17: Line 39:
|A boolean indicating if additional content should be retrieved.  Addiditional content includes the body of emails.
|A boolean indicating if additional content should be retrieved.  Addiditional content includes the body of emails.
|-
|-
|Auth Scheme=NTLM;
|Other="BodyType=Text"; OR Other="BodyType=HTML";
|Sets the scheme used for authentication. Accepted entries are NTLM, BASIC, DIGEST, NONE, NEGOTIATE, and KERBEROSDELEGATION.
|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.
|}
|}


==SQL Selection Statement==
=Mapping=
Use the Query Builder... hyperlink on the right to build your initial queriesCheck out the CData documentation select page for more information about writing queries, http://cdn.cdata.com/help/CEC/ado/pg_select.htm.
==Calendar_RequiredAttendees==
You can only INSERT into this "table".  Simply supply the Email (Address) and (Display) Name of the Calendar Invitee and the Exchange Calendar's ItemId.
 
Optionally, you can populate the SendMeetingInvitations "field". The allowed values are SendToNone, SendOnlyToAll, and SendToAllAndSaveCopy. The default is SendToNone.  NOTE: You can either send no one OR send to every person invited to the meeting. We have to add people one at a time, so if a meeting has four attendees, it invites the first person 4 times, the second person 3 times, the third person 2 times and the last person 1 time. If you want to send invites, you're going to have to somehow set the SendMeetingInvitations "field" on the LAST invitee for that meeting.  If anyone is added to the Calendar item at a later date, all attendees will get an update.  There is no way to send only to the newly added Attendee.
 
==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 - Note that if Exchange is your Origin and you "Use Origin Connector" for your stage, you will have this values in your origin.  If not: [[Exchange_(v2)#Example_Function_to_retrieve_the_ItemId|ItemId]]
#ItemChangeKey - Note that if Exchange is your Origin and you "Use Origin Connector" for your stage, you will have this values in your origin.  If not: [[Exchange_(v2)#Example_Function_to_retrieve_the_ItemChangeKey|ItemChangeKey]]
#FolderDisplayName - The name of the folder to move the email to.
 
==Delete Email==
Create a stage that DELETES to the folder you want to delete fromMatch on the ItemId and ItemChangeId.
 
==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.
<syntaxhighlight lang="vb">
Sub VBScriptProcedure
    Logmessage SmartLookup("GetAttachment", "Content", "@@ORG:AttachmentId@@")
End Sub
</syntaxhighlight>
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===
<syntaxhighlight 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
</syntaxhighlight>
 
===Example Function to retrieve the ItemId===
<syntaxhighlight lang="vb">
Function ScriptedField
dim json, itemid
ParseJSON("{}")
json = XrefRead("InforToExchangeIDs", "@@ORG:Key@@")
ParseJSON json
id = GetJSON("itemid")
ScriptedField=id
End Function
</syntaxhighlight>


=Mapping=
===Example Function to retrieve the ItemChangeKey===
<syntaxhighlight lang="vb">
Function ScriptedField
dim json, itemId, changeKey
ParseJSON("{}")
json = XrefRead("InforToExchangeIDs", "@@ORG:Key@@")
ParseJSON json
'I don't trust the users to not edit the items in Exchange, so I look up the ChangeKey every time as opposed to using my xref.
itemId = GetJSON("itemid")
If itemId <> "" Then
changeKey = SmartLookup("Calendar","ItemChangeKey"," ItemId = '" & itemId & "'")
End If
'If for some reason the lookup failed, I try to get the changeKey from the xref.
If changeKey = "" OR IsNull(changeKey) Then
changeKey = GetJSON("changekey")
End If
ScriptedField=changeKey
End Function
</syntaxhighlight>

Latest revision as of 22:25, 7 November 2019

Overview

The "Exchange (NEW)" connector is based on a driver from https://www.cdata.com/. Helpful documentation for using the "Exchange (v2)" 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

Calendar_RequiredAttendees

You can only INSERT into this "table". Simply supply the Email (Address) and (Display) Name of the Calendar Invitee and the Exchange Calendar's ItemId.

Optionally, you can populate the SendMeetingInvitations "field". The allowed values are SendToNone, SendOnlyToAll, and SendToAllAndSaveCopy. The default is SendToNone. NOTE: You can either send no one OR send to every person invited to the meeting. We have to add people one at a time, so if a meeting has four attendees, it invites the first person 4 times, the second person 3 times, the third person 2 times and the last person 1 time. If you want to send invites, you're going to have to somehow set the SendMeetingInvitations "field" on the LAST invitee for that meeting. If anyone is added to the Calendar item at a later date, all attendees will get an update. There is no way to send only to the newly added Attendee.

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 - Note that if Exchange is your Origin and you "Use Origin Connector" for your stage, you will have this values in your origin. If not: ItemId
  2. ItemChangeKey - Note that if Exchange is your Origin and you "Use Origin Connector" for your stage, you will have this values in your origin. If not: ItemChangeKey
  3. FolderDisplayName - The name of the folder to move the email to.

Delete Email

Create a stage that DELETES to the folder you want to delete from. Match on the ItemId and ItemChangeId.

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
	'I don't trust the users to not edit the items in Exchange, so I look up the ChangeKey every time as opposed to using my xref.
	itemId = GetJSON("itemid")
	If itemId <> "" Then
		changeKey = SmartLookup("Calendar","ItemChangeKey"," ItemId = '" & itemId & "'")
	End If
	'If for some reason the lookup failed, I try to get the changeKey from the xref.
	If changeKey = "" OR IsNull(changeKey) Then
		changeKey = GetJSON("changekey")
	End If
	ScriptedField=changeKey
End Function