How to Get Office 365 Mail Attachments using SSIS

Introduction

In our previous article, we saw How to Get Office 365 data in Power BI. Now let’s look at How to Get Office 365 Mail Attachments using SSIS without any coding. Microsoft Graph API is a unified way to access many Microsoft services API including Office 365 API. In this post, we will use SSIS JSON / REST API Source Connector to get the Outlook Messages list and get the attachments of those messages.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Use Case of Microsoft Graph API

Here are some use cases why you want to use Microsoft Graph API

  • Read/Write events from Outlook Calendar
  • Send email  / Read emails
  • Get the list of files from OneDrive
  • Upload/ Download files to OneDrive
  • Read/Write Excel Sheet (Use range or a specific range of specific sheet)
  • Read Sharepoint Document library
  • Search content from Sharepoint document library
  • Get AD users for your Organization (AD Accounts)
  • Update OneNote
  • Track changes to Users, Events, Calendar Items using delta API

Register Application (OAuth2 App for Graph API)

Very first step to access any Office 365 API / Graph API is to register OAuth App. It's a few step process. After following steps you will get following 2 items to use for next section.
  1. Application Id
  2. Application Secret
Check this link to learn how to register your app.
Let's look at step by step how to register your App to access API.
  1. Visit ApplicationsList to register your new App. Login using your email which can be Active directory account (work account) or Office 365 account or personal account (e.g. hotmail / outlook.com)
  2. Once you login click "Add an App"
  3. On the next screen fill out App name / contact email and uncheck "Let us help you option" click next
  4. On the App configuration screen make sure following three things done
    1. Under Application Secret click "Generate Password". Save that password somewhere.
    2. Under Platform  section click Add and Select "Web"
    3. Once you add Web Platform you will see redirect URI. Enter following URL
      https://zappysys.com/oauth
    4. All other fields are optional (e.g. App logo, Product URL)
    5. Save page / app registration
Once the above steps are done you can proceed to use Graph API.

Step-By-Step to Call Microsoft Graph Rest API Using SSIS

Now let’s look at examples to access your outlook.com emails using SSIS. You can use the same techniques to call pretty much any Office 365 API.

Configure SSIS OAuth Connection for Graph REST API

The first step to access any Graph API is configured OAuth connection

  1. Open Visual Studio
  2. Open existing SSIS Project or create new using File > New > Project > Choose “Integration Services Project” Type under Business Intelligence template category
  3. Create Package
  4. Right-click inside the Connection Managers area and click “New Connection…”
  5. From the connection type list select “ZS-OAUTH” connection type.
    Create new SSIS OAuth API Connection Manager

    Create new SSIS OAuth API Connection Manager

  6. On the OAuth, Connection Manager configure the following options
    1. Select “Custom” from Provider dropdown
    2. Select OAuth2 from OAuth version
    3. Enter your Application Id and Secret Key (i.e. App Password) obtained in the previous section from here.
    4. Enter the following URL in the Authorization URL
    5. Use following URL in the token URL field
    6. Enter the following Graph API Scopes (each scope must be entered in a new line).
      NOTE: Always include offline_access scope returns refreh_token which allows you to renew token without going through the login process again. For more information about which scopes needed always refer to the API help page (e.g. if you want to read mail using this API then you will see it says you can use Mail.Read or Mail.ReadWrite scope).
    7. Go to Advanced tab and enter the following URL in the Callback/Return URL (Assuming exact same URL used when you registered App in the previous section)
    8. Now go back to the first tab and click Generate Token. When prompted Login using your Personal Microsoft Account or Work Account (Office 365 or AD login)
    9. After login, you will see Accept option just click it. If things go right then you will see Access Token and Refresh Token fields will be populated.
  7. Once all configured you can click Test to make sure connection works.
    SSIS OAuth COnnection Manager for Office 365 REST API / Microsoft Graph API

    SSIS OAuth Connection Manager for Office 365 REST API / Microsoft Graph API

NOTE: Typically RefreshToken is longed lived but in some cases, they may expire soon too. If that’s the case then you can configure above OAuth connection for Changing RefreshToken pattern. Simply enter the token file path and re-authenticate by clicking Generate Token. After that each time you make an API call it will save a new refresh token in the file.

Download the Messages Attachment Files from Outlook

Let’s start with an example. We use the SSIS JSON Source and Web API Destination components to make the call to Microsoft Graph Rest API.

  1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

    Drag and Drop : Data Flow Task from SSIS Toolbox

  2. From the SSIS toolbox drag and drop JSON Source on the data flow designer surface.
    Drag and Drop JSON Source Component

    Drag and Drop JSON Source Component

  3. Double click JSON Source and enter the following URL as below to Get Messages API call with search options to get the mails which have the attachments.
    Set the filter as $.value[*]  and click on the preview.

    SSIS JSON Source – Get data from Microsoft Graph API (Office 365 API – Read Mail Example)

    SSIS JSON Source – Get data from Microsoft Graph API (Office 365 API – Read Mail Example)

    To customize URL with additional parameters check this help link. It’s a standard OData Protocol so you can leverage many common OData features (e.g. Use $top parameters to set pageSize. Default is 10 for List Mail but you can include more records per response by changing it such as …/messages?$top=50).

  4.  Now let’s use Template Transform to set the URL using the message ID to make the Get attachment API call.

    SSIS Template Transfrom

    SSIS Template Transform

  5. Now we need to use Web API Destination to make the Get Attachment API call by message-id. Select the Input column for URL as that TemplateOutput.
    SSIS Web API Destination – Get data from Microsoft Graph API (Office 365 API – Get Attachment Example)

    SSIS Web API Destination – Get data from Microsoft Graph API (Office 365 API – Get Attachment Example)

  6. Now next step is to parse the JSON Response String of get attachment api call, for that we need to use JSON Parser. Select the input as Web PAI Destination ResponseText and use the below sample JSON string to configure the metadata(Columns).
    JSON Parser Transfrom

    JSON Parser Transform

  7.  Now in the JSON Parser go-to columns Tab and set the contentBytes  DataType as DT_IMAGE  and increase others’ columns DT_WSTR length with 1000 and 1500. We set contentBytes to DT_IMAGE  datatype its like varbinary(MAX) .
    JSON Parser Transform Columns Tab

    JSON Parser Transform Columns Tab

  8. Furthermore, on the next step, we need to use SSIS Derived Column to set the FilePath where we want to save the attachments and concat the local file path with the attachment name column.
    SSIS Derived Column : Add New Column

    SSIS Derived Column: Add New Column

  9. Finally, we need to use the Export Column Transform (Native) to export attachment bytes to a local file. It needs to know 2 upstream info Bytes and Target File Path.
    Export Column

    Export Column

  10. Make sure to attach the export column to some destination (e.g. Our ZS Trash Destination) else it might be removed by the engine while It will optimize the runtime And it may remove all transformations that don’t have Destination like deploying the package to SQL Server or if you set optimize in the Visual Studio.
  11. That’s it execute the package and it will download all the email attachments.
    Downloaded Mail Attachments

    Downloaded Mail Attachments

Conclusion

After all, In this article, we have learned how to make Microsoft Graph API Rest API call. We used SSIS JSON / REST API Connector to extract data from Outlook Mail using OAuth. Made the Get attachment API call using the Web API Destination and parsed the JSON response using the JSON Parser. And using Native SSIS Export Column we saved export attachment bytes to a local file. To explore many other scenarios not discussed in this article download SSIS PowerPack from here (includes 70+ Components).

References

Finally, you can use the following links for more information:

Posted in REST API, SSIS JSON Parser Transform, SSIS JSON Source (File/REST), SSIS OAuth Connection, SSIS PowerPack, SSIS Template Transform, SSIS Trash Destination, SSIS WEB API Destination and tagged , , , , .