Working with Microsoft Graph API in SSIS using KingswaySoft Productivity Pack

26 January 2022
KingswaySoft Team

Microsoft Graph is a RESTful web API that enables you to access various Microsoft Cloud service resources. In this blog post, we will look at working with Microsoft Graph API using OData Connection Manager and Premium OData components offered in our SSIS Productivity Pack product. This post will demonstrate how to query the SharePoint data with Microsoft Graph API using the Premium OData Source component.

Configure OData Connection Manager

To connect to Microsoft Graph through the OData web service, the first step is to configure the OData Connection Manager to establish the connection to Microsoft Graph.

The Base URL for Microsoft Graph should have an address in the following format:

  • https://graph.microsoft.com/{version}

Microsoft Graph currently supports two versions: v1.0 and beta. The v1.0 service endpoint includes APIs that are generally available, while the beta one includes APIs that are under preview. It’s always recommended to use the generally available API for production use.

Image 001 - Graph OData Connection Manager

Once the Base URL is specified, the next step is to configure the authentication method to connect to Microsoft Graph. This can be done in the Authentication tab. Authenticating with the Microsoft Graph API should be done using an OAuth2 bearer token option. There are multiple types of OAuth2 authentication supported in Microsoft Graph API, the authorization code grant (access on behalf of a user) and client credentials (access as the app and not on behalf of a user) grant are supported.

Authenticating on behalf of a user

You would use the OAuth2 authentication method. it is required to register an Azure Active Directory application in the Azure portal so that your app can be integrated with the Microsoft identity platform and make Microsoft Graph API service calls. Once the app has been created, you would need to grant the app the necessary API permissions to have access to the Graph data. We are accessing SharePoint sites data in this blog post, so we need to make sure the Graph delegated permission “Sites.Manage.All” is added to the app.

Image 002 - AAD App Permission for Graph Access - User

Once you have the Microsoft AAD app registered 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 that is shipped within the SSIS Productivity Pack software. You should already have all the required information to fill in the OAuth Token Generator form. As we have just mentioned, the OAuth Token Generator is a tool shipped along with all other components within the SSIS Productivity Pack product, and it can be launched from the OData or HTTP connection manager provided by KingswaySoft. Based on relevant Microsoft documentation on Graph API, the following should be what it looks like in the OAuth Token Generator after everything has been filled:

Image 003 - Graph 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 Sites.Manage.All

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 launch the authorization page. From there you would log in and provide your consent to the permissions the app is requesting. Once done, you will be redirected to the redirect_uri web page that you have set up in the Microsoft AAD app. The URL will be appended with an authorization code in its code query string parameter. You would now copy the entire URL back to the pop-up dialog in OAuth Token Generator if you are using the Authorize In Browser mode, and then you will be navigating to the Request Tokens page from where you can complete the process of obtaining a token for connection purposes.

Image 004 - Graph OAuth Request Token

In the Request Tokens page, Request Token URL should be “https://login.microsoftonline.com/{tenant-id}/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. If everything goes well, you should now have received the OAuth access_token along with a refresh_token. Now it is a good time to save the entire token as a file to your local file system, which can be used for your connection manager later. When doing so, you will be prompted to provide a password to protect the sensitive fields in the token file.

Image 005 - Graph OData Connection Manager OAuth2 Token Authentication

Once the OAuth2 token file has been successfully generated and specified in the OData connection manager, the connection manager will automatically manage the lifecycle of the token, including the automatic refresh of the token when the current access token has expired. This provides you the maximum convenience for an uninterrupted integration process when it is used for unattended SSIS job executions. You can request the token file from one computer system, but upload it to another server for runtime execution.

Authenticating without a user

Microsoft Graph supports a different authentication flow, which is the OAuth2 client credential. When doing so, our software will try to obtain the access token from Azure AD using the client credentials provided in the connection manager, which would then make Microsoft Graph service calls as the app identity. When using the client credential authentication flow, it’s required to add application permissions in the AAD app. The screenshot below shows the permission configuration that is required in order to access SharePoint sites data. Essentially, you will need the “Sites.Manage.All” application permission.

Image 006 - AAD App Permission for Graph Access - App

Once the app permission has been properly configured, you can now configure the OData Connection Manager in the way that it should request an access token through OAuth 2.0 client credentials authentication flow using the Azure OAuth authentication mode.

Image 007 - Graph OData Connection Manager Azure OAuth Authentication

The Azure OAuth authentication mode is designed to work with various Azure services, including the support of making a POST service request to the /token identity platform endpoint to acquire an access token using client_credential grant type with a shared secret or a certificate. As illustrated above, you would simply provide the Azure tenant id, client id, and client secret of the AAD App, with a scope value of "https://graph.microsoft.com/.default" to access the Graph resource data.

After the authentication has been properly configured, we would navigate to the OData Settings tab and choose “Generic – ‘Next Link Paging’” service implementation because Microsoft Graph uses the ‘@odata.nextLink’ value in response for pagination.

Image 008 - Graph OData Connection Manager Settings

Finally, if everything has been set up correctly, click the Test Connection button at the bottom left of the editor to make sure that the connection works. If everything goes well, you should receive a “Test connection succeeded” message as shown below.

Image 009 - Graph OData Connection Manager Test Connection

Configuring OData Source to read Graph data

After successfully creating and testing our OData connection, we are now ready to actually interact with the API to facilitate reading and writing Microsoft Graph data. This can be achieved using the Premium OData Source component which is offered in the SSIS Productivity Pack product. The Premium OData Source component is an enhanced OData Source component that supports working with entity, collection of entities, bound functions and unbound functions. It does a lot more than alternative solutions that might be available.

Get SharePoint Site Id in Graph API

According to Microsoft Graph API documentation, the SharePoint siteId format is a composite of the <HostName,SPSite.ID,SPWeb.ID>. It’s easy to work with the root site as the “root” identifier can be used as the siteId to reference the root site. To get the siteId value for other sites if you don’t have the SPSite.ID and SPWeb.ID values but you know the site name, you can use the Search endpoint in Premium OData Source component to find sites that match with the provided search term. The Premium OData Source component configuration below searches across a SharePoint tenant for sites that match with the provided keyword “TestSite”.

Image 010 - OData Source Search SharePoint Sites

Get SharePoint Site Lists in Graph API

Once the SharePoint siteId is retrieved, we can easily get the SharePoint lists in the given site in the Graph API. OData supports getting related entities for a particular relationship. The screenshot below shows the example of retrieving all SharePoint lists for a particular site.

Image 011 - OData Source Get SharePoint Lists

Get SharePoint Site List Items in Graph API

Similar to get SharePoint lists on a site, we can also get the related items of a given list by configuring the Premium OData Source component like the following screenshot:

Image 012 - OData Source Get SharePoint ListItems

As shown in the screenshot above, we are also expanding the ‘fields’ resource to retrieve the related list item metadata for a list item.

Conclusion

In this blog post, we have demonstrated how to consume SharePoint site data with Microsoft Graph API using the Premium OData Source component available in SSIS Productivity Pack. Microsoft Graph API is the programmatic way to retrieve the tremendous amount of data that resides in various Microsoft services, including SharePoint. Using the same strategy, you can similarly use our Premium OData components to work with other Graph data such as Calendar, Planner, etc.

Archive

November 2024 3 October 2024 1 September 2024 1 August 2024 2 July 2024 1 June 2024 1 May 2024 1 April 2024 2 March 2024 2 February 2024 2 January 2024 2 December 2023 1 November 2023 1 October 2023 2 August 2023 1 July 2023 2 June 2023 1 May 2023 2 April 2023 1 March 2023 1 February 2023 1 January 2023 2 December 2022 1 November 2022 2 October 2022 2 September 2022 2 August 2022 2 July 2022 3 June 2022 2 May 2022 2 April 2022 3 March 2022 2 February 2022 1 January 2022 2 December 2021 1 October 2021 1 September 2021 2 August 2021 2 July 2021 2 June 2021 1 May 2021 1 April 2021 2 March 2021 2 February 2021 2 January 2021 2 December 2020 2 November 2020 4 October 2020 1 September 2020 3 August 2020 2 July 2020 1 June 2020 2 May 2020 1 April 2020 1 March 2020 1 February 2020 1 January 2020 1 December 2019 1 November 2019 1 October 2019 1 May 2019 1 February 2019 1 December 2018 2 November 2018 1 October 2018 4 September 2018 1 August 2018 1 July 2018 1 June 2018 3 April 2018 3 March 2018 3 February 2018 3 January 2018 2 December 2017 1 April 2017 1 March 2017 7 December 2016 1 November 2016 2 October 2016 1 September 2016 4 August 2016 1 June 2016 1 May 2016 3 April 2016 1 August 2015 1 April 2015 10 August 2014 1 July 2014 1 June 2014 2 May 2014 2 February 2014 1 January 2014 2 October 2013 1 September 2013 2 August 2013 2 June 2013 5 May 2013 2 March 2013 1 February 2013 1 January 2013 1 December 2012 2 November 2012 2 September 2012 2 July 2012 1 May 2012 3 April 2012 2 March 2012 2 January 2012 1

Tags