How to use Office 365 API with SSIS (Mail, Calendar, Contacts, OneDrive, Excel)

NOTE: UPDATE: ZappySys has released a brand new API Connector for SSIS Outlook Mail (Office 365) which makes it much simpler to download attachment, read / search Emails, Users, MailFolders, Send email and more in SSIS compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

In this post you will learn how to access Microsoft Graph API (Office 365 REST API / Sharepoint API) inside 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 load data from Office 365 to SQL Server.

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 list of files from OneDrive
Upload/ Download files to OneDrive
Read/Write Excel Sheet (Use range or 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)

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 -

Log into Microsoft Azure portal to register a custom app.
Register a new application by clicking New Registration link.
Provide the name of the custom app and who can access the app in the organization.
Go to the App overview and add a Redirect URL.
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
```
Create a Client Secret key which will be used to Authenticate the custom Azure app.
Note - Take a note of Client Secret, it will be required while configuring OAuth connection in the SSIS later
Add API Permissions.
Select following permissions from the Delegated Permissions section.
```
User.ReadBasic.All
Files.Read
offline_access
```
Take a note of Client ID, it will be required while configuring OAuth connection in the SSIS later.

Step-By-Step : Access Outlook.com Emails using SSIS

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

Configure SSIS OAuth Connection for Graph REST API

First step to access any Graph API is configure OAuth connection

Open Visual Studio
Open existing SSIS Project or create new using File > New > Project > Choose “Integration Services Project” Type under Business Intelligence template category
Open Package
Right click inside Connection Managers area and click “New Connection…”
From the connection type list select “ZS-OAUTH” connection type.
Create new SSIS OAuth API Connection Manager
On the OAuth Connection Manager configure 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 following URL in the Authorization URL
  
  https://login.microsoftonline.com/common/oauth2/v2.0/authorize
  
  1
  
  https://login.microsoftonline.com/common/oauth2/v2.0/authorize
5. Enter the following URL in the token URL field
  
  https://login.microsoftonline.com/common/oauth2/v2.0/token
  
  1
  
  https://login.microsoftonline.com/common/oauth2/v2.0/token
6. Enter the following Graph API Scopes (each scope must be entered in a new line).
  
  user.read mail.read offline_access
  
  1
  2
  3
  
  user.read
  mail.read
  offline_access
  
  NOTE: Always include offline_access scope returns refreh_token which allows you to renew token without going through login process again. For more information about which scopes needed always refer to 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)
  
  https://zappysys.com/oauth
  
  1
  
  https://zappysys.com/oauth
8. Now go back to 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.
Once all configured you can click Test to make sure connection works.
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 token file path and re-authenticate by clicking Generate Token. After that each time you make an API call it will save new refresh token in file.

Configure SSIS JSON/REST API Source

Once you configure OAuth connection you are ready to read data from Office 365 API. Lets look at step by step how to read emails using Mail API and save to SQL Server.

Drag and drop Data flow task from SSIS toolbox
Goto data flow designer and drag and drop ZS JSON Source/REST API Connector on the designer
Double click to edit JSON Source.
In the URL field enter below URL

https://graph.microsoft.com/v1.0/me/messages

1

https://graph.microsoft.com/v1.0/me/messages

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).
Check Use credentials option
From the connections dropdown select OAuth Connection manager created in the previous section
Change Data Format option to OData
In the Filter enter following or click “Select Filter” and select Value Array node > Click OK.

$.value[*]

1

$.value[*]
In the Max rows set to 200 so we don’t pull all data for now 🙂 … This is our hello world example.
Now click Preview to see data. If all is well then Save UI by clicking OK
SSIS JSON Source – Get data from Microsoft Graph API (Office 365 API – Read Mail Example)

Loading Graph API / Office 365 data to SQL Server

Now last step is loading Outlook data to SQL Server. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors

To load Office 365 data into SQL Server perform the following steps

Drag OLEDB destination on Data Flow surface
Connect ZS JSON Source to Destination
On OLEDB destination select / create new SQL Connection and then Click “New Table”
Click on Mappings tab and click OK to save
Execute Package

Configure SSIS OLEDB Destination – Loading Xero Data into SQL Server Table

Xero to SQL Server Column Mappings for OLEDB Destination

Graph API / Office 365 to SQL Server Column Mappings for OLEDB Destination

Loading Graph REST API / Office 365 data to SQL Server in SSIS

POST data to Graph API (Insert or Update)

So far we have seen how to read data from Office 365 API. Now let’s look at how to write data to Office 365.

There are two ways you can achieve this.

Use Web API Destination in Data Flow
Use REST API Task in Control Flow

Read this article to learn more about API POST using SSIS

Using SSIS Web API Task (Write data from SQL Server to Office 365 / Graph API)

Using REST API Task to POST data to Graph API

If you have JSON/XML data already prepared and if you like to POST it to Graph API then REST API Task would be easy to use.

REST API Task Body can be direct string, variable (e.g. {{User::varSomeData}} or Body can come from File

SSIS REST API Task - POST data to Xero (Create contacts)

SSIS REST API Task – POST data to Graph API

Download file from OneDrive

To download file from OneDrive perform these steps

https://zappysys.zendesk.com/hc/en-us/articles/115005010573-How-to-download-file-from-OneDrive-using-Graph-API

Upload file to OneDrive

https://zappysys.zendesk.com/hc/en-us/articles/115004893713-How-to-upload-file-to-OneDrive-using-Office-365-Graph-API

Reading Office Excel Sheets from OneDrive

JSON Source also support reading API response which returns 2D arrays. Such as Office Excel Spreadsheet API to read excel data. Check this blog post to learn similar scenario to parse 2D arrays. There are few other options for parsing 2D arrays which are documented here.

1	https://graph.microsoft.com/v1.0/me/drive/items/01CYZLFJDYxxxxxxx/workbook/worksheets('test')/range(address='A1:B200')

Testing Office 365 API using Graph API Explorer

If you like to test API request/response inside browser then Microsoft provides really great tool to called Graph API Explorer. Click here to try Graph API Explorer.

See below screenshot

Common Errors

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

Conclusion. What’s next?

In this article we have learned how to load data from Microsoft Graph API to SQL Server using SSIS ( drag and drop approach without coding). We used SSIS JSON / REST API Connector to extract data from Outlook Mail using OAuth. JSON Source Connector makes it super simple to parsing complex / large JSON Files or any Web API Response into rows and column so you can load into database like SQL Server. Download SSIS PowerPack to try many other automation scenarios not discussed in this article.

Keywords: