Working with SharePoint Online through OData v4 Endpoint

29 June 2021
KingswaySoft Team

SharePoint provides two kinds of web service interfaces: SOAP and REST web services. When compared to the SOAP web service, the REST web service is lightweight as it supports JSON format while the SOAP web service always works with XML format. Our dedicated SharePoint toolkit is built based on SOAP web service which supports most of the data integration needs. However, when taking performance and some advanced features into consideration, the REST API would be a preferred solution. SharePoint REST API is built based on the standard Open Data Protocol (OData) syntax. This post will show you how to query the SharePoint REST API data using the Premium OData Source component that we have in our SSIS Productivity Pack product.

Our Premium OData components are primarily designed to work with OData v4 services, so we would need to work with SharePoint OData v4 endpoints.

Configure OData Connection Manager

To connect to SharePoint Online through OData web service, the first step is to configure the OData Connection Manager to connect to your SharePoint instance.

SharePoint ODataV4 Base URL should have an address in the following formats:

  • https:// {site_url}/_api/
  • https:// {site_url}/sites/{site_name}/_api/

Image 001 - SharePoint OData Connection Manager

Once the Base URL is specified, the next step is to configure the authentication method to connect to the SharePoint server. This can be done in the Authentication tab. You would use the OAuth2 authentication method. it is required to register an Azure Active Directory application in the same Azure tenant. Once the app has been created, you would need to grant the app with the necessary API permissions in order to grant the access to SharePoint data. The permission configuration should be something below.

Image 002 - AAD App Premission for SharePoint Access

Once you have the Microsoft AAD app created and configured, you can come back to the connection manager, and set the authentication mode to OAuth 2 in OData Connection Manager, from where you should be able to launch the OAuth Token Generator. You should already have all the required information to put into the OAuth Token Generator, which is a tool shipped within the SSIS Productivity Pack product, and it can be launched from within the OData or HTTP connection manager. Based on Microsoft OAuth documentation, we should configure the OAuth Token Generator to look like something below:

Image 003 - SharePoint OAuth Token General Setting

Parameter

Value

Sign In Url

https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/authorize

Client Id

The application ID of the AAD app.

Client Secret

The application secret in the AAD app.

Scope

offline_access https://{sharepoint_server_url}/AllSites.FullControl

Redirect URI

The redirect_uri configured in the AAD app

Click the Next button to go to the next page and click either the Authorize In App… or Authorize In Browser… button to start the authorization process, from the launched webpage, you can login and provide consent to the permissions the app is requesting. Once this is done, you will be redirected to the redirect_uri web page that you have set up in the Microsoft AAD app. The redirected page will have a URL that includes an authorization code. Copy the entire URL to the pop-up dialog if you are using the Authorize In Browser mode, then you will be navigated to the Request Tokens page in OAuth Token Generator.

Image 004 - SharePoint OAuth Request Token

The Request Token URL is “https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token” for Microsoft OAuth authentications. Fill in the Request Tokens URL and click the Get Tokens button to request an access token, once the OAuth access_token and refresh_token are successfully retrieved, save the token file to your local file system with a proper password. All sensitive information will be encrypted with the provided password in the token file.

When the OAuth2 token file is successfully generated and used in OData Connection Manager, the Connection Manager automatically refreshes the access_token using the refresh_token upon expiry in the future, this includes your runtime execution, provided that the token file is made available to the server.

Image 005 - SharePoint OData Connection Manager Authentication

After setting up the authentication, you would navigate to OData Settings tab and choose “SharePoint” as the service implementation, which is designed to closely follow SharePoint REST API specification that has been documented.

Image 006 - SharePoint OData Connection Manager Settings

Finally, if everything has been set up correctly, clicking the Test Connection button at the bottom left of the editor should return a “Test connection succeeded” message as shown below.

Image 007 - SharePoint OData Connection Manager Test Connection

Configuring OData Source for SharePoint Online

After we have successfully created and tested our OData connection, we are ready to begin reading SharePoint Online data through the Premium OData Source component. Premium OData Source component is an enhanced OData Source component that supports working with a particular entity, a collection of entities, bound functions and unbound functions.

Query SharePoint Online data with filters

The screenshot below shows an example OData source component that queries list items with filtering conditions, so it only retrieves items that were created after a certain date.

Image 008 - OData Source Query SharePoint List Items

The OData Query Builder also supports using SSIS variables in the filter condition.

Image 009 - OData Source Query Builder

Expand to return related data

OData V4 supports expand functionality to query related data from associated entities. For example, we can use $expand=File to get the File data for each List Item record. When expand is used, you’re effectively doing a SQL JOIN - it is important to note that specifying too many expand items can lead to huge result sets and may significantly slow down the reading performance.

Image 010 - OData Source Expand SharePoint data

With a configuration like the screenshot above, we are getting the id=1 List Item record along with its related file data.

Invoke an Unbound Function

OData supports custom operations (Actions and Functions). Functions are operations exposed by an OData service that MUST return data and MUST NOT have any observable side effects. Actions are operations exposed by an OData service that MAY have side effects when invoked. Functions and actions both MAY bound to an entity type, primitive type, complex type, or a collection.

In our Premium OData components, custom functions are supported in Premium OData Source component and actions are supported in Premium OData Destination, previously we have written a blog post, Consuming Dynamics 365 Business Central/NAV Codeunit Services using KingswaySoft OData Components, which demonstrates how to invoke unbound actions in Premium OData Destination component. In this blog post, we will demonstrate how to invoke functions in Premium OData Source component to read SharePoint Online data.

Unbound functions are not bound to any entity typies and they are also referred as static operations. In this next example, we’ll call GetFileByServerRelativeUrl unbound function, this function will return the file record for a given server file path value.

Image 012 - OData Source Unbound function

Invoke a Bound Function

Bound functions are bound to an entity or a collection of entities. The configuration below returns the checked out files in a specific list. The function GetCheckedOutFiles() is bound to List.

Image 011 - OData Source bound function

Conclusion

In conclusion, working with SharePoint data through OData web service in SSIS cannot be any easier when using the KingswaySoft Productivity Pack components, your integration can be done without writing a single line of code.  Premium OData Source component offers a lot of features including filtering, sorting, expanding and bound/unbound functions support. In summary, the OData components in our SSIS Productivity Pack product compliments our SharePoint toolkit by offering some unique flexibilities.

Archive

Tags