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


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


Get the Exchange Web Services Managed API from here 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;

    #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());


    #region create CalendarView

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


    #region retrieve responce

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

    if (findItemResponse == null)


    #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 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.

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

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


      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:// 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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s