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/
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.
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:
|
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.
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.
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.
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.
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.
The OData Query Builder also supports using SSIS variables in the filter condition.
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.
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.
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.
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.