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!