Video Tutorial - Integrate Outlook Mail (Office 365) data in SSIS
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download / install required driver for
Outlook Mail (Office 365) integration in SSIS - How to configure connection for
Outlook Mail (Office 365) - Features about
API Source (Authentication / Query Language / Examples / Driver UI) - Using
Outlook Mail (Office 365) Connection in SSIS
Prerequisites
Before we perform steps listed in this article, you will need to make sure following prerequisites are met:- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is downloaded and installed (download it). Check Getting started section for more information.
Read data from Outlook Mail (Office 365) in SSIS (Export data)
In this section we will learn how to configure and use Outlook Mail (Office 365) Connector in API Source to extract data from Outlook Mail (Office 365).
-
Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.
-
In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link. -
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
-
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
-
Select New Connection to create a new connection:
-
Use a preinstalled Outlook Mail (Office 365) Connector from Popular Connector List or press Search Online radio button to download Outlook Mail (Office 365) Connector. Once downloaded simply use it in the configuration:
Outlook Mail (Office 365) -
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]
Steps how to get and use Outlook Mail (Office 365) credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g.automation@my-company.com
). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
- Use
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Use
https://zappysys.com/oauth
as the URL.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
Mail.Read
-
Mail.Read.Shared
-
Mail.ReadBasic
-
Mail.ReadBasic.Shared
-
Mail.ReadWrite
-
Mail.ReadWrite.Shared
-
Mail.Send
-
Mail.Send.Shared
-
User.Read
-
User.ReadBasic.All
-
- Press Generate Token button to generate Access and Refresh Tokens.
- Optional step. Choose Default User Id from the drop down menu (if someone shared a mailbox with you).
- Click Test Connection to confirm the connection is working.
- Done! Now you are ready to use the API Connector!
Configuring authentication parameters
Outlook Mail (Office 365)User Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill-in the parameter... Token URL Fill-in the parameter... Client ID Fill-in the parameter... Scope Fill-in the parameter... Return URL Fill-in the parameter... Optional Parameters Client Secret Refresh Token File Path Login Prompt Option Default User Id (Select after clicking **Generate Token**) me RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503 RetryCountMax 5 RetryMultiplyWaitTime True Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]
Steps how to get and use Outlook Mail (Office 365) credentials
Follow these simple steps below to create Microsoft Entra ID application with application access permissions.
- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
- e.g. select
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Leave the URL field empty.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Mail (Outlook) permissions:
-
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Choose Default User Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- That's it! Now you can use the API Connector!
Configuring authentication parameters
Outlook Mail (Office 365)Application Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Token URL Fill-in the parameter... Client ID Fill-in the parameter... Client Secret Fill-in the parameter... Scope Fill-in the parameter... Default User Id Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503 RetryCountMax 5 RetryMultiplyWaitTime True -
Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.
API Source - Outlook Mail (Office 365)Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more. -
That's it! We are done! Just in a few clicks we configured the call to Outlook Mail (Office 365) using Outlook Mail (Office 365) Connector.
You can load the source data into your desired destination using the Upsert Destination, which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV, Excel, Azure Table, Salesforce, and more. You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)
Write data to Outlook Mail (Office 365) using SSIS (Import data)
In this section we will learn how to configure and use Outlook Mail (Office 365) Connector in the API Destination to write data to Outlook Mail (Office 365).
Video tutorial
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download SSIS PowerPack for
Outlook Mail (Office 365) integration in SSIS - How to configure connection for
Outlook Mail (Office 365) - How to write or lookup data to
Outlook Mail (Office 365) - Features about SSIS API Destination
- Using
Outlook Mail (Office 365) Connector in SSIS
Step-by-step instructions
In upper section we learned how to read data, now in this section we will learn how to configure Outlook Mail (Office 365) in the API Source to POST data to the Outlook Mail (Office 365).
-
Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
-
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
-
Select New Connection to create a new connection:
API Destination - Outlook Mail (Office 365)Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more. -
Use a preinstalled Outlook Mail (Office 365) Connector from Popular Connector List or press Search Online radio button to download Outlook Mail (Office 365) Connector. Once downloaded simply use it in the configuration:
Outlook Mail (Office 365) -
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]
Steps how to get and use Outlook Mail (Office 365) credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g.automation@my-company.com
). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
- Use
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Use
https://zappysys.com/oauth
as the URL.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
Mail.Read
-
Mail.Read.Shared
-
Mail.ReadBasic
-
Mail.ReadBasic.Shared
-
Mail.ReadWrite
-
Mail.ReadWrite.Shared
-
Mail.Send
-
Mail.Send.Shared
-
User.Read
-
User.ReadBasic.All
-
- Press Generate Token button to generate Access and Refresh Tokens.
- Optional step. Choose Default User Id from the drop down menu (if someone shared a mailbox with you).
- Click Test Connection to confirm the connection is working.
- Done! Now you are ready to use the API Connector!
Configuring authentication parameters
Outlook Mail (Office 365)User Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill-in the parameter... Token URL Fill-in the parameter... Client ID Fill-in the parameter... Scope Fill-in the parameter... Return URL Fill-in the parameter... Optional Parameters Client Secret Refresh Token File Path Login Prompt Option Default User Id (Select after clicking **Generate Token**) me RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503 RetryCountMax 5 RetryMultiplyWaitTime True Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]
Steps how to get and use Outlook Mail (Office 365) credentials
Follow these simple steps below to create Microsoft Entra ID application with application access permissions.
- Navigate to the Azure Portal and log in using your credentials.
- Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
INFO: Find more information on how to register an application in Graph API reference.
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
- e.g. select
-
Supported account type
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
. - Leave the URL field empty.
- Set the type to
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Mail (Outlook) permissions:
-
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Choose Default User Id from the drop down menu.
- Click Test Connection to confirm the connection is working.
- That's it! Now you can use the API Connector!
Configuring authentication parameters
Outlook Mail (Office 365)Application Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Token URL Fill-in the parameter... Client ID Fill-in the parameter... Client Secret Fill-in the parameter... Scope Fill-in the parameter... Default User Id Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429|503 RetryCountMax 5 RetryMultiplyWaitTime True -
Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - Outlook Mail (Office 365)Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more. -
Finally, map the desired columns:
API Destination - Outlook Mail (Office 365)Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more. -
That's it; we successfully configured the POST API Call. In a few clicks we configured the Outlook Mail (Office 365) API call using ZappySys Outlook Mail (Office 365) Connector
Load Outlook Mail (Office 365) data into SQL Server using Upsert Destination (Insert or Update)
Once you read data from the desired source, now let's see how to Load Outlook Mail (Office 365) data in SQL Server using Upsert Destination. Upsert Destination can Merge/Synchronize data from source to target for Microsoft SQL Server, PostgreSql and Redshift. It supports very fast Bulk Upsert (Update or Insert) operation along and Bulk delete.
-
From the SSIS toolbox drag and drop Upsert Destination on the dataflow designer surface
Connect our Source component to Upsert Destination
-
Double click on Upsert Destination component to configure it.
-
Select the desired Microsoft SQL Server/PostgreSql/Redshift Target Connection or click NEW to create new connection. Select Target Table or click NEW to create new table based on source columns.
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS -
Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
-
Click on OK to save Upsert Destination settings UI.
-
That's it. Run the SSIS Package and it will read the data from the Outlook Mail (Office 365) and load the data in the SQL Server/PostgreSql/Redshift using Upsert Destination.
Deploy and schedule SSIS package
After you are done creating SSIS package, most likely, you want to deploy it to SQL Server Catalog and run it periodically. Just follow the instructions in How to design, debug, deploy, schedule SSIS Package (In SQL Agent and Catalog) article to see how to do it.
Advanced topics
Actions supported by Outlook Mail (Office 365) Connector
Outlook Mail (Office 365) Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.Parameter | Description |
---|---|
Table Name |
|
Parameter | Description |
---|---|
User ID or Email |
|
Parameter | Description |
---|---|
User ID or Email |
|
Parameter | Description |
---|---|
Mail Folder Id |
|
Parameter | Description |
---|---|
User ID or Email |
|
Parameter | Description |
---|---|
User ID or Email |
|
Parameter | Description | ||||||
---|---|---|---|---|---|---|---|
Message Id |
|
||||||
Email Body Format |
|
Parameter | Description |
---|---|
User ID or Email |
|
Message ID |
|
Parameter | Description |
---|---|
User ID or Email |
|
Message ID |
|
Attachment ID |
|
Parameter | Description |
---|---|
User ID or Email |
|
Message ID |
|
Attachment ID |
|
Parameter | Description | ||||||
---|---|---|---|---|---|---|---|
User ID or Email |
|
||||||
MailFolderId |
|
||||||
OverwriteFile |
|
||||||
SaveFolder |
|
Parameter | Description | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Url |
|
||||||||||||||||||||||||||
Body |
|
||||||||||||||||||||||||||
IsMultiPart |
|
||||||||||||||||||||||||||
Filter |
|
||||||||||||||||||||||||||
Headers |
|
Parameter | Description |
---|---|
Url |
|
IsMultiPart |
|
Filter |
|
Headers |
|
Conclusion
In this article we discussed how to connect to Outlook Mail (Office 365) in SSIS and integrate data without any coding. Click here to Download Outlook Mail (Office 365) Connector for SSIS and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).
Download Outlook Mail (Office 365) Connector for SSIS
Documentation
More integrations
Other application integration scenarios for Outlook Mail (Office 365)
Other connectors for SSIS
Download Outlook Mail (Office 365) Connector for SSIS
Documentation
How to connect Outlook Mail (Office 365) in SSIS?
How to get Outlook Mail (Office 365) data in SSIS?
How to read Outlook Mail (Office 365) data in SSIS?
How to load Outlook Mail (Office 365) data in SSIS?
How to import Outlook Mail (Office 365) data in SSIS?
How to pull Outlook Mail (Office 365) data in SSIS?
How to push data to Outlook Mail (Office 365) in SSIS?
How to write data to Outlook Mail (Office 365) in SSIS?
How to POST data to Outlook Mail (Office 365) in SSIS?
Call Outlook Mail (Office 365) API in SSIS
Consume Outlook Mail (Office 365) API in SSIS
Outlook Mail (Office 365) SSIS Automate
Outlook Mail (Office 365) SSIS Integration
Integration Outlook Mail (Office 365) in SSIS
Consume real-time Outlook Mail (Office 365) data in SSIS
Consume real-time Outlook Mail (Office 365) API data in SSIS
Outlook Mail (Office 365) ODBC Driver | ODBC Driver for Outlook Mail (Office 365) | ODBC Outlook Mail (Office 365) Driver | SSIS Outlook Mail (Office 365) Source | SSIS Outlook Mail (Office 365) Destination
Connect Outlook Mail (Office 365) in SSIS
Load Outlook Mail (Office 365) in SSIS
Load Outlook Mail (Office 365) data in SSIS
Read Outlook Mail (Office 365) data in SSIS
Outlook Mail (Office 365) API Call in SSIS