Microsoft Office 365 Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Microsoft Office 365 Version Support

All hosted versions of Microsoft Office 365 are supported via the microsoft Graph API v1.0. Includes information accessible from 365 editions of Exchange/Outlook, Teams, Tasks, and OneDrive.

Establish a Connection

Connect to Microsoft Office 365

Authenticate using Microsoft Entra service principal

Microsoft Entra service principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Microsoft Entra service principal authentication for an authentication guide.

Create an Entra ID (Microsoft Entra ID) Application

Create an Entra ID (Microsoft Entra ID) Application

Microsoft Office 365 supports OAuth-based authentication using Microsoft Entra ID. If you will connect via a web application and want to authenticate via Entra ID, you must first register a custom OAuth application in the Entra Admin Center, as described below.

If you will connect via a desktop application or headless machine, you can authenticate using Microsoft Office 365's built-in embedded application credentials, which use branding. However, custom OAuth applications are also compatible with desktop and headless authentication flows, and may be preferable for production deployments or environments requiring strict policy control.

Register the Application

To register an OAuth application in Microsoft Entra ID, follow these steps:

1. Go to https://portal.azure.com.

2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.

3. Click New registration.

4. Enter a name for the application.

5. Specify the types of accounts this application should support:

   • For private-use applications, select Accounts in this organization directory only.
   • For distributed applications, select one of the multi-tenant options.

   Note

   If you select Accounts in this organizational directory only, when you connect with Microsoft Office 365 connector, you must set AzureTenant to the tenant's ID (either GUID or verified domain). Otherwise, authentication will fail.

6. Set the redirect URI to http://localhost:33333 (default), or use another URI appropriate for your deployment.

   When using a custom redirect URI set a CallbackURL connection property; in those cases, set it to match this URI exactly.

7. Click Register. The application management screen opens. Record these values for later use:

   • Application (client) ID is used for OAuthClientId
   • Directory (tenant) ID is used for AzureTenant

8. Go to Certificates & Secrets. Click New Client Secret, set the desired expiration, and save the generated value. This value will only be shown once — record it to use with OAuthClientSecret.

9. Select the Microsoft Graph API and then select the permissions your app will seek.

10. To confirm, click Add permissions.

Create a Service Principal App in Entra ID (Microsoft Entra ID)

Create a Service Principal App in Entra ID (Microsoft Entra ID)

Microsoft Office 365 supports Service Principal-based authentication, which is role-based. This means that the Service Principal's permissions are determined by the roles assigned to it. The roles specify what resources the Service Principal can access and which operations it can perform.

If you want to use a Service Principal to authenticate to Microsoft Office 365, you must create a custom application in Microsoft Entra ID.

To enable Service Principal authentication:

• Confirm that you have permission to register applications and assign roles in your tenant.
• Register a new application and configure credentials and permissions in the Entra Admin Center.

Register the Application

1. Go to https://portal.azure.com.
2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup. Since this custom application is for Service Principal use, choose Any Microsoft Entra ID tenant – Multitenant.
6. Click Register. The application management screen opens. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant
7. Navigate to Certificates & Secrets and define the application authentication type. Two types of authentication are available: certificate (recommended) or client secret
   • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine. For more information on creating a self-signed certificate, see Create a self-signed certificate
   • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft Office 365 displays the key value. This value is displayed only once, so be sure to record it for future use. Use this value for the OAuthClientSecret
8. Navigate to Authentication and select the Access tokens option.
9. Select the Microsoft Graph API and then select the permissions your app will seek.
10. Save your changes.
11. If you specified permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page.

Grant Admin Consent

Some custom applications require administrative permissions to operate within a Microsoft Entra ID tenant. This is especially true for applications that use Application permissions, which allow the app to run without a signed-in user. Admin consent can be granted when creating a new application, by adding relevant permissions marked as "Admin Consent Required".

Admin consent is also required to use Client Credentials in the authentication flow.

These permissions must be granted by an admin. To grant admin consent:

1. Log in to https://portal.azure.com with an administrator account.
2. Navigate to Microsoft Entra ID > App registrations and select your registered application.
3. Navigate to API permissions.
4. Review the permissions listed under Application permissions. Ensure the necessary API scopes are included for your use case.
5. Click Grant admin consent to approve the requested permissions.

This gives your application permissions on the tenant under which it was created.

Consent for Client Credentials

OAuth supports the use of client credentials to authenticate. In a client credentials authentication flow, credentials are created for the authenticating application itself. The auth flow acts just like the usual auth flow, except that there is no prompt for an associated user to provide credentials. All tasks accepted by the application are executed outside of the context of a default user.

1. Log in to https://portal.azure.com
2. Create a custom OAuth application, as described above.
3. Navigate to App Registrations.
4. Find the application you just created, and open API Permissions.
5. Select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated and Application.
6. For use with Service Principal, specify Application permissions.
7. Select the permissions you require for your integration.

Administrative Tasks

The Microsoft Office 365 connector can be used to perform administrative tasks. This can be done by specifying the UserId column to execute CUD operations.

The UserId Column

Many tables expose a special UserId column. This is designed to be used by an administrator to modify records on another user's account. If you are not an administrator or do not desire this behavior, do not specify the UserId when performing an INSERT / UPDATE / DELETE operation. For instance, executing the following will insert a contact for another user:

INSERT INTO Contacts (displayName, CompanyName, UserId) VALUES ('Bill', 'Bob Co', '12345')

The above request will have the overall effect of attempting to add a contact under the resource at /users/12345/contacts. When UserId is not specified, the resources affected will instead be modified under /users/me/contacts. In general if you are not an administrator, you can only affect or view records under /users/me, so it is not recommended to set UserId when you are not an admin.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Microsoft Office 365 connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft Office 365 and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Microsoft Office 365 connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Events WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

The Microsoft Office 365 connector models Microsoft Office 365 objects as an easy-to-use SQL database, using tables, views, and stored procedures. These are defined in schema files, which are simple, easy-to-read text files that define the structure and organization of data. Because the table definitions are dynamically retrieved, any changes to the remote data are immediately reflected in your queries.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples from a sample Office 365 site. Your data model is obtained dynamically based on your user credentials and Office 365 site.

The sample site includes the following tables:

Stored Procedures

Stored Procedures are SQL scripts that extend beyond standard CRUD operations. They can be used to search, update, and modify information in Office365.

Tables

The connector models the data in Microsoft Office 365 as a list of tables in a relational database that can be queried using standard SQL statements.

Microsoft Office 365 Connector Tables

Calendars

Provides details about calendars associated with users, including names, time zones, and ownership metadata. Maps dynamically to the API fields.

Table Specific Information

Select

You can query Calendars by specifying an ID or selecting all:

SELECT * FROM Calendars WHERE ID = 'your Calendar ID goes here'

Select a certain column from the entity and filter by that column:

SELECT ID FROM Calendars WHERE name LIKE 'Calendar%'

Insert

Specify a Name as a minimum in order to create a new Calendar:

INSERT INTO Calendars (Name) VALUES ('John')

INSERT INTO Calendars (Name, UserId) VALUES ('Test123', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe');

Columns

Contacts

Contains user contact records, including names, email addresses, job titles, and business phone numbers synced from Office365.

Table Specific Information

Select

You can query Contacts by specifying an ID or selecting all:

SELECT * FROM Contacts WHERE ID = 'your Contact ID goes here'

Select a certain column from the entity and filter by that column:

SELECT GivenName FROM Contacts WHERE GivenName LIKE 'John%'

Insert

Specify a GivenName and a Surname as a minimum in order to create a new Contact:

INSERT INTO Contacts (GivenName, Surname) VALUES ('John', 'Smith')

INSERT INTO Contacts (GivenName, Surname, UserId) VALUES ('John', 'Smith', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe')

Columns

Conversations

Stores threaded conversations from Microsoft 365 Groups, including messages and participants.

Table Specific Information

Select

The GroupId is required to get group Conversations.

SELECT * FROM Conversations WHERE GroupId = 'your GroupId goes here'

You can also get group Conversations by using the GroupId and the Conversation Id.

SELECT * FROM Conversations WHERE ID = 'conversation ID here' AND GroupId = 'your GroupId goes here'

Insert

Specify GroupId, Topic, Content, and NewParticipants to create a new Conversation. NewParticipants is a complex type. Its format is as follows: 'name1, email1; name2, email2'.

INSERT INTO Conversations (GroupId, Topic, Content, NewParticipants) VALUES ('GroupId here', 'This is a test topic.', 'Hi, How Are you?', 'someone, someone@example.com')

Columns

Events

Captures core details of calendar events, such as start and end times, locations, organizers, and recurrence rules. Maps dynamically to API fields.

Table Specific Information

Select

The 'me' property is used by default to return events.

You can use the UserId in the WHERE clause to override this when searching for Events.

SELECT * FROM Events WHERE UserId = 'abc123' AND Subject LIKE '%test%'

The GroupId can be a calendar type ID or a group Id. For example:

SELECT * FROM Events WHERE GroupId = 'enter your group ID here'

Insert

To create a new event, start and end are required, including the timezone.

INSERT INTO Events (Subject, Body_Content, Start_DateTime, Start_TimeZone, End_DateTime, End_TimeZone) VALUES ('New Test Event', 'Event created using Office365Provider', '2016-01-01T10:00:00', 'UTC', '2016-01-01T11:00:00', 'UTC')

Note: By default this statement will create your event under the default calendar.

INSERT INTO Events (Subject, Body_Content, Start_DateTime, Start_TimeZone, End_DateTime, End_TimeZone, UserId) VALUES ('New Test Event', 'Event created using Office365Provider', '2016-01-01T10:00:00', 'UTC', '2016-01-01T11:00:00', 'UTC', '92dfdfc6-f1d4-4965-9f71-30e4da4fa7fe')

Columns

Files

Lists files stored in OneDrive or SharePoint, with metadata including file names, locations, last modified times, and sharing status.

Table Specific Information

Select

Retrieve files by using the UserId or File ID (Id) for instance, or simply filter by a certain column:

SELECT * FROM Files WHERE UserId = 'MyUserId'

SELECT Name, LastModifiedDateTime FROM Files WHERE Name LIKE 'test%'

To work for Folder-level files, we need to specify the parentReference_path in the query.

SELECT * FROM files WHERE parentReference_path = '/drives/b!3LIvU2zISEqicGlWkgVknKxKT-q7gM5IqlBJ4w4MZqaX6BQc_vtwQpnqaldXkH9I/root:/Test_Shubham';

INSERT

INSERT operation is not supported for this table.

Note: See UploadFile (or CreateFolder to create a folder) to insert and update content to a file.

Columns

Groups

Includes metadata about Microsoft 365 Groups, such as group names, descriptions, email aliases, and visibility settings.

Table Specific Information

Groups require Administrator permissions. To work with them, you must create your own custom OAuth App and set the appropriate OAuthClientId and OAuthClientSecret. In this app, you must configure it to request the Group.Read.All and the Group.ReadWrite.All permissions. This can be done at https://apps.dev.microsoft.com, or in the App Registrations panel at http://portal.azure.com. See Creating an Entra ID (Microsoft Entra ID) Application for more details on creating a custom app.

To authorize Groups permissions, an administrator must grant the Groups permissions for your organization at large. This can be done via the administrator authorization endpoint. Simply have the administrator navigate to the following web page and grant permissions. Then run the OAuth authorization as normal afterwards.

https://login.microsoftonline.com/common/adminconsent?client_id=[YourClientId]&redirect_uri=http://localhost:33333

Note that if your organization has multiple tenants, you may replace the /common/ in the URL with the tenant ID to indicate which tenant to grant permissions for.

Select

Retrieve all groups, specify a GroupId (Id), or simply filter by a certain column:

SELECT * FROM Groups WHERE Id = 'Group Id here'
SELECT Id, Description, DisplayName FROM Groups WHERE DisplayName = 'test'

Insert

The following are required to create a new Security Group:

INSERT INTO Groups (DisplayName, MailEnabled, MailNickname, SecurityEnabled) VALUES ('Test group', false, 'test', true)

Columns

Messages

Contains email messages from user mailboxes, including subjects, senders, timestamps, and read status.

Table Specific Information

Select

You can retrieve all from Messages, specify a Message (Id), UserId, or ParentFolderId, or you can filter results by a certain column:

SELECT * FROM Messages WHERE Id = 'MyMessageId'

SELECT * FROM Messages WHERE UserId = 'MyUserId'

SELECT * FROM Messages WHERE ParentFolderId = 'MyParentfolderId'
SELECT * FROM Messages WHERE ParentFolderId = 'Drafts'
SELECT DisplayName, ID FROM Users WHERE DisplayName LIKE 'John%'

Insert

After the INSERT, a new Message will be created in the User's Drafts folder.

INSERT INTO Messages (Subject, Body_Content, UserId) VALUES ('New test Email', 'Test Email created.', 'User ID goes here')

Note: To send the mail, see SendMail.

Update

To update a message:

UPDATE Messages SET Subject = 'Email Updated', Body_Content = 'New Body Content' WHERE ID = 'MyMessageId'

Preserving HTML Formatting

If you are executing an INSERT or UPDATE and your message has HTML in its body_content, you must set body_contentType to 'html'.

UPDATE Messages SET body_content = '<my html document>', body_contentType = 'html'

If you don't set body_contentType to 'html', the HTML content of the message (such as elements and tags) will be visible in the body of the resulting email as plaintext instead of being processed as HTML.

Known Issues

This table may return an inconsistent number of results. That is, it can return a number of rows for one query and a different numbers of rows in subsequent queries, even when your messages remain unchanged. This means that some messages may be missing when querying this table.

This is a known bug in the Microsoft Graph API.

There is a workaround that allows the connector to retrieve all messages, but it comes with a tradeoff: 'events' and 'contacts' data will be returned along with 'messages' data. When using this workaround, you must use filtering to distinguish between message and non-message rows.

To enable this workaround, add "ClientSidePaging=true;" (without quotation marks) in the value of the Other connection property.

Columns

Tasks

Lists individual tasks from Microsoft To Do or Planner, with details like titles, due dates, and completion status.

Table Specific Information

Tasks requires the Groups and Tasks permissions from the Microsoft Graph. For this reason, you must create your own OAuth App. Please see Creating an Entra ID (Microsoft Entra ID) Application for more details.

Select

By default, if no criteria is specified, only Tasks personally assigned to you will show up. For example:

SELECT * FROM Tasks

To bring back tasks across the organization, provide the specific plans ids, or use a subselect for the plan id. For example:

SELECT * FROM Tasks WHERE PlanId IN (SELECT ID FROM Plans)

Insert

To insert a Task, the associated plan must be specified:

INSERT INTO Tasks (Title, PlanId) VALUES ('My Title', '99999999-eeeeeeeee')

Update

To update a Task, both the ID and Etag must be specified:

UPDATE Tasks SET Title = 'New Title' WHERE ID = 'xxxxxx-AAAAAAAAAAA' AND Etag = 'W/\"XXXXXXQEBAQEBAQEBAQEBAQEBARCc=\"'

Delete

To delete a Task, both the ID and Etag must be specified:

DELETE FROM Tasks WHERE ID = 'xxxxxx-AAAAAAAAAAA' AND Etag = 'W/\"XXXXXXQEBAQEBAQEBAQEBAQEBARCc=\"'

Columns

Users

Supports reading, creating, updating, and deleting Office365 user accounts. Includes profile and licensing details.

Table Specific Information

Select

Query the Users table by retrieving everything from Users, specifying a Id, or filtering by a column:

SELECT * FROM Users WHERE Id = '616391f0-32d8-4127-8f25-aa55771d6617'

SELECT DisplayName, ID FROM Users WHERE DisplayName LIKE 'John%'

Insert

The following are required to create a new organizational User:

INSERT INTO Users (AccountEnabled, DisplayName, MailNickname, UserPrincipalName, PasswordProfile_ForceChangePasswordNextSignIn, PasswordProfile_Password) VALUES (false, 'John Smith', 'JohnS', 'smithjohn@yourcompanydomain.com', true, '123password')

Columns

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Microsoft Office 365 Connector Views

CalendarView

Returns a filtered list of calendar events such as occurrences, exceptions, and single instances, within a specified time range from a user's calendar.

Table Specific Information

Select

Get the occurrences, exceptions, and single instances of events in a calendar view defined by a time range, from the user's default calendar, or from some other calendar of the user's. By default only the event occurrences from the user's default calendar in the range of the last 30 days will be returned. You can filter results by CalendarId, UserId, Start_DateTime, End_DateTime.

For example the following queries will be processed server side:

SELECT * FROM CalendarView WHERE Start_DateTime >= '2019-12-10 15:00' AND End_DateTime <= '2020-01-10 14:30'

SELECT * FROM CalendarView WHERE CalendarId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAAACC_IAAAA='

SELECT * FROM CalendarView WHERE CalendarId = 'AQMkAGRlMWQ5MDg0LWI5ZTQtNDk2Yi1hOTQ1LTU4YzFmMzEwZjlhMgBGAAAD-FjxR3cIwE6TEGSCVtIHcwcAQyR2Iw3coEOaUD1BLt0tnAAAAwcAAABDJHYjDdygQ5pQPUEu3S2cAAACC_IAAAA=' AND UserId = 'a98f25b5-5da1-4937-8729-c0d03026caa0' AND Start_DateTime >= '2019-12-15 08:00' AND End_DateTime <= '2020-01-14 08:00'

Columns

EventAttachments

Displays attachments related to calendar events, including file metadata and associated event identifiers.

Columns

EventOccurrences

Provides a flattened view of recurring event instances, enabling analysis of each occurrence individually.

Table Specific Information

Select

You can query EventOccurrences by specifying the Event Id, StartDatetime and EndDateTime. EventId is a required field, instead StartDatetime and EndDateTime have a default range of the last 30 days. If you query filtering only by EventId and the specific event does not exist within this time range, you will get empty results.

SELECT * FROM [EventOccurrences] WHERE ID = 'event id' AND StartDateTime = '2018/01/01' AND EndDateTime = '2018/12/31'

By default, if StartDateTime and EndDateTime filters are not specified, only the event occurrences from the user's default calendar in the range of the last 30 days will be returned. Otherwise, the query will get the Occurrences of the Event during the period specified by StartDateTime and EndDateTime.

Columns

MessageAttachments

Retrieves email message attachments with metadata like attachment names, sizes, and parent message IDs.

Columns

Plans

Displays task plan data from Microsoft Planner, including plan names, owners, and associated group IDs.

Table Specific Information

Using Plans requires access to Groups permissions. This requires Admin approval. For this reason, you must use your own OAuth App to add the Groups permissions and from the Microsoft Graph. See Creating an Entra ID (Microsoft Entra ID) Application for more details.

Select

All plans in MS Planner exist as a part of a group. In order to retrieve the list of available plans, you must retrieve a list of available plans per group. If no GroupId is specified, then the following WHERE condition will be appended to any query:

GroupId IN (SELECT ID FROM Groups)

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft Office 365.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft Office 365, along with an indication of whether the procedure succeeded or failed.

Microsoft Office 365 Connector Stored Procedures

AddAttachments

Adds one or more attachments to an existing email message based on message ID.

Input

Result Set Columns

AssignLicense

Assigns or removes Microsoft 365 subscriptions for a user and enables or disables specific service plans within those subscriptions.

Input

Result Set Columns

CancelEvent

Cancels an existing calendar event and updates the event status across invitees.

Input

Result Set Columns

CreateFolder

Creates a new folder or updates the contents of an existing file in OneDrive or SharePoint.

Input

Result Set Columns

CreateSchema

Generates and saves a schema definition file for a specified Office365 table or view.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Result Set Columns

DeleteAttachment

Removes an attachment from an email message based on the message and attachment IDs.

Input

Result Set Columns

DownloadAttachments

Downloads one or more attachments from a specified email message.

Input

Result Set Columns

DownloadEmail

Downloads the full contents of an email message, including metadata and body content.

Input

Result Set Columns

DownloadFile

Downloads a specified file from OneDrive or SharePoint.

Input

Result Set Columns

FetchAdditionalUserFields

Retrieves additional Tier 1, Tier 2, and Tier 3 user fields for enhanced profile data.

Input

Result Set Columns

ForwardEvent

Forwards a calendar event invitation to one or more recipients.

Input

Result Set Columns

ForwardMail

Forwards an existing email message to specified recipients.

Input

Result Set Columns

GetAdminConsentURL

Returns a URL to initiate the admin consent process for granting application access using custom OAuth credentials.

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from Office365.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the auth token from this URL.

Input

Result Set Columns

MoveMail

Moves an email message to a specified folder within a user's mailbox.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various Office 365 services.

Input

Result Set Columns

SendMail

Sends an email message from the authenticated user's mailbox with optional attachments.

Input

Result Set Columns

UploadFile

Uploads a new file or replaces the content of an existing file in OneDrive or SharePoint.

Input

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft Office 365:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Events table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Events'

Columns