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

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”

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

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

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

Open the Propertied window for the Script Component

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

change to the Inputs and Outputs page

click the Add Output button

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

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.

This work is licensed under a Creative Commons Attribution Share Alike license.