Calling SharePoint 365 REST API in SSIS

Introduction to REST API with SharePoint in SSIS

UPDATE: ZappySys has released a brand new API Connector for SharePoint Online which makes it much simpler to Read/Write SharePoint Data in SSIS compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.

Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).

icon SharePointUsing REST API with SharePoint in SSIS is a common requirement to Administer SharePoint. SharePoint 365 is a nice Microsoft application in the Cloud used to share documents and collaborate with the company. You can have schedules, projects, documents and more shared using a Web platform.

In addition, it is a very useful tool that your company may need. It is also possible to automate, administer task using Rest API.
This article, we will learn how to create REST API queries to SharePoint 365 using the Microsoft Graph API using SSIS. To learn about other Office 365 REST API in SSIS check this article (Outlook, Onedrive, mail, Excel API).

Also, we will show our ZappySys SSIS PowerPack that includes very powerful tools to export SharePoint data from REST API to any other source.

Requirements to REST API with SharePoint

  1. First, you will need SSDT installed.
  2. Second, SSIS PowerPack installed.
  3. An Office 365 Account with SharePoint 365

Getting started

Microsoft included the Microsoft Graph to easily create API queries to different Microsoft components like OneDrive, Excel, Insights, Microsoft Team, Planner and more.

The Microsoft Graph is a gateway to access to 365 Data in an integrated platform. Let’s start with the Microsoft Graph Explorer and do some REST API queries to understand how to use REST API in SharePoint.

Microsoft Graph Explorer to SharePoint introduction

The best way to learn REST API with SharePoint 365 is to use the Graph Explorer. You can run API queries using the Graph Explorer and get your data. Here we will show how.

  1. First, we will go to the Graph Explorer and sign-in with your Office 365 Account.
  2. Secondly, in Run Query, you can specify your query, which will show SharePoint information.
    graph api explorer

    Graph Explorer

  3. Let start with this link:
  4. This shows properties about site resources. The response of the query is in JSON format:
    graph api microsoft json results

    Graph API Microsoft JSON results

  5. Also, it is possible to enable examples for SharePoint Sites, SharePoint Lists, and other Microsoft technologies:
    microsoft graph api enable api applicatins

    Graph API Applications

Create an OAuth connection to REST API with SharePoint in SSIS

    1. First, to get the Data from the Microsoft Graph, we will sign in to create an application in the Azure Portal app registration.
    2. Secondly, create a Microsoft Application. It will provide you an Application ID and a Password. For detailed steps about how to create a Microsoft Application, check our article related.
      microsoft graph api application id password

      Microsoft Graph API application id password

    3. Once that the application is created, go to SSDT or Visual Studio and create a new connection.
       oauth connection ssis

      SSIS OAuth connection

    4. Select the ZS-OAuth connection. Once selected, enter the Client ID (the Microsoft Graph Application ID), the client secret (application password).
    5. Add the Authorization URL is the following:
    6. Also, include the Access token URL is the following:
    7. The following permissions were applied in the example: User.Read
      NOTE: It is advised to include offline_access scope in order to get a Refresh Token and to be free of any token restrictions.
    8. Once that the properties are entered, press the Generate token button:
      oauth graph API with REST API with SharePoint in SSIS

      SSIS OAuth with REST API with SharePoint in SSIS

How to call SharePoint API in SSIS

  1. Once that your OAuth Connection is created, we can use it in a REST API task. This task is included with the ZappySys SSIS PowerPack:
     rest api ssis task

    SSIS REST API task

  2. Secondly, in the REST API Task, select the option URL from connection and select the OAuth connection created before. We will first use the following URL to see the list of site properties:
  3. Also, press Test request/Response button to get the results of the query:
    ssis sharepoint rest api call

    SSIS SharePoint rest API call

  4. The response will be in JSON format:
    rest api ssis sharepoint 365 response

    REST API SSIS Sharepoint 365 response

  5. Also, it is possible to store the results in a file or a variable:
    SSIS save API SharePoint response to file REST API with SharePoint

    SSIS save API SharePoint response to a file REST API with SharePoint

  6. In Addition, you can run the following query to enumerate all the sites:
  7. Also, to show the information on a specific site, the following query will be used:
  8. For example, if you site id is the URL would be the following:
  9. To enumerate sites, the following example can be useful:
  10. Also, to enumerate lists, you can use the following URL:

How to read SharePoint lists in SSIS using REST API

We will learn how to show SharePoint lists using rest API.

In addition, you can get the list ids using this query:

SSIS SharePoint list API

SharePoint list API

How to read data from a Sharepoint List

  1. To get the list of ithems form a list use the following query:
  2. Finally, to get metadata from a list, you can use the following query. To learn more how to import Sharepoint List into SQL Server use JSON / REST API Source.
 sharepoint ssismetadata list

SSIS Sharepoint metadata list

How to create a SharePoint list item in SSIS using REST API (POST Method)

It is also possible to POST data using REST API. In the next example, we will create a new list in SharePoint 365 using REST API.

  1. First, in SSDT, use the REST API Task used previously and specify the following URL:
    Where is the site id and mynewlist is a new list created. Also, in the HTTP Request Method, use the POST method.
  2. In addition, go to the Body Request, we will create a widget. We will use the following JSON data and make sure to select JSON (application/json) in the body:
    Post SharePoint REST API

    SharePoint Post REST API

  3. Finally, if everything is successful, you will be able to see the new item created:
    sharepoint ssis list created post

    SSIS SharePoint list created with a POST

How to export SharePoint API response results to a file

We have a nice task named JSON Source used to get data from REST API or a JSON file and export to any source the results like SQL Server, Excel, a flat file, etc.
In this example, we will export data from the JSON Source to a JSON file using the JSON Destination Task.

  1. First of all, we will use the Data Flow. Drag and drop the data flow to the design pane and double click to go to the Data Flow tab:
     Data Flow SSIS

    SSIS Data Flow

  2. Secondly, in the Data Flow tab, drag and drop the JSON Source, the JSON Destination and join them:
     json source to json destination ssis

    SSIS JSON source to JSON destination

  3. In addition, in the JSON Source, we will check the option Use Credentials and select the OAuth connection created and used before.
  4. Enter Path or Web URL, we will use the following URL:
  5. Also, We will use the following filter:
     json ssis source REST API with SharePoint in SSIS

    SSIS JSON Source REST API with SharePoint in SSIS

  6. In addition, go to JSON Destination task, select mappings and right click on add elements:
    json destination ssis

    SSIS JSON Destination

  7. Also, select the attributes:
     add attributes ssis

    SSIS add attributes

  8. Finally, run the package to export the data.

Conclusion about REST API with SharePoint in SSIS

To conclude, we can say that SharePoint 365 is a very popular application to share documents and information. With REST API it is possible to get data properties and administer SharePoint. We used the REST API, JSON Source to get and send information. If you want to try yourself, you can download our tools here.


Posted in REST API Integration, SSIS JSON File Destination, SSIS JSON Source (File/REST), SSIS PowerPack, SSIS REST API Task, SSIS XML File Destination and tagged , , , , , , .