Download Attachments from Exchange Web Services using SSIS

I recently worked on an engagement where the requirement was to process excel spreadsheets sent to a group mailbox hosted in an on prem exchange server. The excel sheets were based on a standard template and had a reasonable enough level of validation to support data quality with loading into a mart. The interesting part of the engagement was building a custom task to process the emails and attachments sent to the group mail box. The choices for this solution were either a 3rd party tool or script task. The client didn’t want to purchase a 3rd party tool…so time to get my C# on.

You can download a sample package containing the script task here: http://ow.ly/MTEN3005InE

The script task is mostly a refactoring of the Exchange 101 code sample available here: https://code.msdn.microsoft.com/exchange/Exchange-2013-Get-d16d35bf

I altered the console app to use the default network credentials, so the task can run under a service account / proxy in SSIS. The script will also handle multiple emails with multiple attachments, the only thing you need to provide are the variable values for the following:

EmailAddr — email account

FileExtension — what file extension types to download from emails

OutputDir — Directory that you want the attachments saved to

Schedule the task to run at your desired interval. It will query the mailbox to find all emails with the attached file type, download the attachments to the output directory, and then delete the email(s).

Important to note that you must have the Microsoft Exchange Web Services Managed API 2.2 installed in your SSIS environment as well. Download available here: https://www.microsoft.com/en-us/download/details.aspx?id=42951

Hope this helps!

 

 

Advertisements

12 thoughts on “Download Attachments from Exchange Web Services using SSIS

  1. Hi, I’m looking for exactly what you have created, but the link doesn’t work.
    Can you provide a working example?

    Like

  2. Hello Jared,

    Thank you for your answers. I tried to open the example but I can’t, I receive the error (“Package Load error 0xC0010014 in CPackage::LoadFromXML”. Do you know why ? I work with the VS 64bit.

    Thank you,
    Evandro

    Like

  3. Hello Jared,

    I just ran into the issue that we want your very helpful code with an Exchange 2013SP1 server. The Microsoft Exchange Web Services Managed API 2.2 contains only the versions up Exchange2010_SP1 in the namespace Microsoft.Exchange.WebServices.Data.

    What should we do?

    Thanks

    Like

      • Hi Jared,

        thanks for the quick reply! Not yet. After the post I did some research and found the Exchange2010_SP1. I was thinking of using it and waited for your reply.

        Keep you updated if it’s working or not.

        Thanks and Brgds,
        Robert

        Like

  4. Hi Jared,

    just ran the script but it isn’t downloading any attacmnent at all but it marked as a successful run. Do you have an idea why?

    Like

  5. Hi Jared,
    Thanks for this++
    It throws an unhandled exception at
    service.AutodiscoverUrl(userData.EmailAddress, RedirectionUrlValidationCallback);
    if you let it try and work with your default fake EmailAddr value. Otherwise it found and saved some attachments first time after I gave it an address that it could successfully autodiscover 🙂

    Like

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