How to Get Office 365 Mail Attachments using SSIS

Updated on by ZappySys
Contents hide
1 Introduction
2 Prerequisites
3 Use Case of Microsoft Graph API
4 Register Application (OAuth2 App for Graph API)
5 Step-By-Step to call the Microsoft Graph Rest API Using SSIS
5.1 Configure SSIS OAuth Connection for Graph REST API
5.2 Download the Messages Attachment Files from Outlook
6 Conclusion
7 References
7.1 More from ZappySys Blog

Introduction

UPDATE: ZappySys has released a brand new Outlook Mail (Office 365) connector, which makes it much simpler to download attachments, read/search emails, users, mail folders, send email, and more in SSIS compared to the steps listed in this article. You can still use the steps from this article, but if you are new to APIs or want to avoid the learning curve, use a newer approach.

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, including the Office 365 API. In this post, we will use the SSIS JSON/REST API Source Connector to retrieve the Outlook Messages list and its attachments.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the 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, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Use Case of Microsoft Graph API

Here are some use cases for why you want to use the 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 a specific sheet)
  • Read the SharePoint Document library
  • Search content from the SharePoint document library
  • Get AD users for your Organization (AD Accounts)
  • Update OneNote
  • Track changes to Users, Events, Calendar Items using the delta API

Register Application (OAuth2 App for Graph API)

The first step to access any Office 365 API / Graph API is to register an OAuth App in the Azure Portal. After following these steps, you will get the following two items to use in the next section:

  • Application Id
  • Application Secret

In this section, you will learn how to register a custom app in Microsoft Azure portal that will allow access to the OneDrive. So, let's get started -

  1. Log into Microsoft Azure portal to register a custom app.
  2. Register a new application by clicking New Registration link.New App Registration in Azure portal
  3. Provide the name of the custom app and who can access the app in the organization.Register an OneDrive App
  4. Go to the App overview and add a Redirect URL.Add a Redirect URL
  5. Click on "Add a Platform" under Platform Configuration section and then select "Web" under Web applications section to enter a Redirect URL. 
    https://zappysys.com/oauth
    Redirect URL
  6. Create a Client Secret key which will be used to Authenticate the custom Azure app.Add a Client SecretSecret Key Expiration PeriodSpecify Secret Key
    Note - Take a note of Client Secret, it will be required while configuring OAuth connection in the SSIS later
  7. Add API Permissions.OneDrive API Permissions
  8. Select following permissions from the Delegated Permissions section. 
    User.ReadBasic.All
Files.Read
offline_access
    Select Delegated Permissions
  9. Take a note of Client ID, it will be required while configuring OAuth connection in the SSIS later.App Client ID

Step-By-Step to call the 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 to configure an OAuth connection

  1. Open Visual Studio
  2. Open an existing SSIS Project or create a new one 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 the ZS-OAUTH connection type.

    Create a new SSIS OAuth API Connection Manager

  6. On the OAuth Connection Manager, configure the following options
    1. Select “Custom” from the Provider dropdown
    2. Select OAuth2 from the 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 the following URL in the token URL field
    6. Enter the following Graph API Scopes (each scope must be entered on a new line).
      NOTE: Always include the offline_access scope, which returns a refresh_token, which allows you to renew the token without going through the login process again. For more information about which scopes are 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 the Advanced tab and enter the following URL in the Callback/Return URL (assuming the same URL used when you registered the App in the previous section)
    8. Now go back to the first tab and click Generate Token. When prompted, log in using your Personal Microsoft Account or Work Account (Office 365 or AD login)
    9. After logging in, you will see the Accept option. Just click it. If things go right, then you will see the Access Token and Refresh Token fields populated.
  7. Once everything is configured, you can click Test Connection to verify the connection works.

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

NOTE: Typically, RefreshToken is long-lived, but in some cases, they may expire soon too. If that’s the case, then you can configure the above OAuth connection to change the refresh token 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 a call to the Microsoft Graph REST API.

  1. First of all, drag and drop the Data Flow Task from the SSIS Toolbox and double-click it to edit.

    Drag and drop Data flow

  2. From the SSIS toolbox, drag and drop the JSON Source onto the Data Flow Designer surface.

    SSIS JSON Source – Drag and Drop

  3. Double-click the JSON Source, then enter the following URL to invoke the Messages API call with search options to retrieve emails with attachments.
    Set the filter to $.value[*] and click the preview.

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

    To customize the 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 Transform sending attachment id

  5. Now we need to use the 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)

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

    JSON Parser Transform – using JSON example

  7.  Now, in the JSON Parser, go to the Go-to columns Tab and set the contentBytes DataType to DT_IMAGE, and increase the other columns’ DT_WSTR lengths by 1000 and 1500. We set contentBytes to the DT_IMAGE  datatype, which is like varbinary(MAX).

    JSON Parser Transform Columns Tab fix the datatype

  8. Furthermore, in the next step, we need to use an SSIS Derived Column to set the FilePath where we want to save the attachments, and then concatenate the local file path with the attachment name 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 the target file path.

    Export Column configuration

  10. Make sure to attach the export column to some destination (e.g., our ZS Trash Destination); the engine might remove it. At the same time, it optimizes the runtime. It may remove all transformations that don’t have a destination, such as deploying the package to SQL Server, or when you set optimize in Visual Studio.
  11. That’s it, execute the package, and it will download all the email attachments.

    Downloaded Mail Attachments

Conclusion

After all, in this article, we learned how to make a Microsoft Graph API REST API call. We used the 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 the Native SSIS Export Column, we saved the export attachment’s 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:

More from ZappySys Blog

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