Reading Appointments from Exchange in SSIS


A fairly comment requirement that requires a little Arcane Black Magic – note that that applies to Exchange 2007 Service Pack 1, but should work with all versions greater than 2007.

Annoyingly you cannot just use the Exchange Web Services for this task, you still use EWS but you have to access them through the Exchange Web Services Managed API

Prerequisites

The account executing the SSIS package must have delegate access to the mailbox you want to read

You must add Microsoft.Exchange.WebServices.dll to the Global Assembly Cache on your development machine

Steps

Get the Exchange Web Services Managed API from here http://www.microsoft.com/download/en/details.aspx?id=13480 and install it on your development machine

Add Microsoft.Exchange.WebServices.dll to the Global Assembly Cache, run CMD as administrator

image

enter cd “C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64” including the quotes

enter gacutil -i “path to dll” the path should be “C:\Program Files\Microsoft\Exchange\Web Services\1.1\Microsoft.Exchange.WebServices.dll”

image

Open Business Intelligence Development Studio (BIDS) or Visual Studio and create a new Integration Service Project, add a Data Flow Task

image

Now add a new user variable to hold the username of the account you are going to access, make sure the scope is package and the type is string

image

open the Data Flow Task and add a Script Component of type Source

image

Open the Propertied window for the Script Component

image

Add the user Variable created above to the Read Only Variables Collection

change to the Inputs and Outputs page

image

click the Add Output button

image

Add output columns for each piece of data you want to output

image

Go Back to the  Script Page and click Edit Script

insert this into the CreateNewOutputRows() method

//TODO: Replace these with your values
NetworkCredential exchangeAccessAccount = new NetworkCredential(@"UserName", @"Password", @"Domain");
Uri OutlookWebAccessUri = new Uri(@"[[Outlook Web Access Url]]/EWS/Exchange.asmx");
DateTime CalanderStart = new DateTime();
DateTime CalanderEnd = new DateTime();
int MaxItemsToReturn = 99999;

try
{
    #region create service binding

    // Create the service binding.
    ExchangeService esb = new ExchangeService(ExchangeVersion.Exchange2007_SP1);
    esb.Credentials = exchangeAccessAccount;
    esb.Url = OutlookWebAccessUri;
    esb.ImpersonatedUserId = new ImpersonatedUserId(ConnectingIdType.PrincipalName, Variables.UserDomainID.ToString());

    #endregion

    #region create CalendarView

    CalendarView calendarView = new CalendarView(CalanderStart, CalanderEnd, MaxItemsToReturn);
    calendarView.PropertySet = PropertySet.IdOnly;

    #endregion

    #region retrieve responce

    // Do the EWS Call...
    FindItemsResults<Appointment> findItemResponse = esb.FindAppointments(WellKnownFolderName.Calendar, calendarView);

    if (findItemResponse == null)
    {
        return;
    }

    #endregion

    #region load atendee data

    //get additional properties for each item returned by view, do this as view cannot return a lot of useful stuff like attendees
    ServiceResponseCollection<ServiceResponse> addProperties = 
esb.LoadPropertiesForItems(from Item item in findItemResponse select item, new PropertySet( BasePropertySet.IdOnly, AppointmentSchema.Resources, AppointmentSchema.RequiredAttendees, AppointmentSchema.OptionalAttendees, AppointmentSchema.Subject, AppointmentSchema.Start, AppointmentSchema.End, AppointmentSchema.IsCancelled )); List<Appointment> additionalProperties = new List<Appointment>(addProperties.Count); if (addProperties != null) { foreach (ServiceResponse currentResponce in addProperties) { additionalProperties.Add(((Appointment)((GetItemResponse)currentResponce).Item)); } } #endregion #region process appts Appointment currentAppointmentAddProps = null; foreach (Appointment currentAppointment in findItemResponse) { #region find additional properties for current Appointment currentAppointmentAddProps = additionalProperties.Find(delegate(Appointment arg) { return arg.Id == currentAppointment.Id; }); #endregion //add data to output here OutputRecordSetBuffer.ActualEndDate = currentAppointmentAddProps.End; } #endregion } catch (Exception e) { }

Important points to note:

  1. the account you specify in exchangeAccessAccount must have delegate access to the account specified in the Variable or this wont work
  2. The MSDN Doc are not reliable, the item.id held in  currentAppointmentAddProps.Id is supposed to be a unique reference to the particular item, in effect it is only unique for this call.  if you run the query again you may but are not gauranteed to get the same id for the same item.
Advertisements

Author: Stuart

Crazy Person

6 thoughts on “Reading Appointments from Exchange in SSIS”

  1. Thanks for the gr8 post. This is exactly what I have been searching for. I’m just a newbee. Followed all your steps but came up with the below error

    Could not find an implementation of the query pattern for source type ‘Microsoft.Exchange.WebServices.Data.FindItemsResults’
    Any suggestions on why this is? I’m using Exchange Web Services Managed API SDK 1.2 since 1.1 which you used has been taken off Microsoft site.

    Thanks in advance
    Vj

    1. It looks like you might be missing some using statements, check that you have all of these:

      System
      System.Data
      System.Net
      Microsoft.SqlServer.Dts.Pipeline.Wrapper
      Microsoft.SqlServer.Dts.Runtime
      Microsoft.SqlServer.Dts.Runtime.Wrapper
      Microsoft.Exchange.WebServices.Data
      System.Collections.Generic
      System.Linq

      1. Thanks heaps 🙂
        That worked and I was able to successfully deploy it after tweaking it some more.

        We have a Vehicle GPS data on SQL Server while vehicle booking and management takes place on Outlook. This article helped me extract the outlook calendar into SQL server and calculate the utilization.

        U Rock!

  2. Thanks. You may find that this barfs if you try and pull more than 800 appointments in a single request – depends on your server spec – but it is easy enough to put it in a loop and grab a few days at a time.

  3. Stuart,
    thank you for the article. really helped. I am stuck at the variable. what I don’t know is what do I need to pass as the ourlookwebaccessuri or what is the value for this variable.
    also if you can give me an example values for user, and domain and userdomainid.

    1. Hi Sayed,
      If you currently use Outlook Web Access then the outlookwebaccessuri will be the website you goto for example htps://webamil.mycompany.com/owa the domain name will be the part of your username before the \ and the username is the part after it.

      you might need to ask you Exchange Admin what they are though.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s