{"id":3674,"date":"2018-05-28T21:56:14","date_gmt":"2018-05-28T21:56:14","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3674"},"modified":"2025-03-19T13:31:46","modified_gmt":"2025-03-19T13:31:46","slug":"sharepoint-365-rest-api-ssis-read-list-write-create","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/sharepoint-365-rest-api-ssis-read-list-write-create\/","title":{"rendered":"Calling SharePoint 365 REST API in SSIS"},"content":{"rendered":"

Introduction to\u00a0REST API with SharePoint in SSIS<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for SharePoint Online<\/a> which makes it much simpler to\u00a0Read\/Write SharePoint Data in SSIS<\/strong> 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.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n

\"icon<\/a>Using 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.<\/p>\n

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

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

\n\n\n\n\n\n

\n\"REST<\/a><\/td>\n		SSIS REST API Web Service Task
\n<\/a><\/td>\n<\/tr>\n

\n\"JSON<\/a><\/td>\n		SSIS JSON Source (File, REST API, OData Connector)<\/a><\/td>\n<\/tr>\n

\n\"JSON<\/a><\/td>\n		SSIS JSON File Destination (Create JSON File)
\n<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Requirements to\u00a0REST API with SharePoint<\/h2>\n
    \n
  1. First, you will need SSDT installed.<\/li>\n
  2. Second, SSIS PowerPack installed.<\/li>\n
  3. An Office 365 Account with SharePoint 365<\/li>\n<\/ol>\n

    Getting started<\/h2>\n

    Microsoft included the Microsoft Graph to easily create API queries to different Microsoft components like OneDrive, Excel, Insights, Microsoft Team, Planner and more.<\/p>\n

    The Microsoft Graph is a gateway to access to 365 Data in an integrated platform. Let\u2019s start with the Microsoft Graph Explorer and do some REST API queries to understand how to use REST API in SharePoint.<\/p>\n

    Microsoft Graph Explorer to SharePoint introduction<\/h3>\n

    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.<\/p>\n

      \n
    1. First, we will go to the Graph Explorer<\/a> and sign-in with your Office 365 Account.<\/li>\n
    2. Secondly, in Run Query, you can specify your query, which will show SharePoint information.\n
    3. Let start with this link:\n
      https:\/\/graph.microsoft.com\/v1.0\/sites\/root\/lists<\/pre>\n<\/li>\n
    4. This shows properties about site resources. The response of the query is in JSON format:\n
    5. Also, it is possible to enable examples for SharePoint Sites, SharePoint Lists, and other Microsoft technologies:\n
      \"microsoft<\/a>
      Graph API Applications<\/p><\/div><\/li>\n<\/ol>\n
      Create an OAuth connection to REST API with SharePoint in SSIS<\/h3>\n
        \n
      1. \n
          \n
        1. First, to get the Data from the Microsoft Graph, we will sign in to create an application in the Azure Portal app registration.<\/a><\/li>\n
        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<\/a>.\n
        3. Once that the application is created, go to SSDT or Visual Studio and create a new connection.\n
        4. Select the ZS-OAuth connection. Once selected, enter the Client ID (the Microsoft Graph Application ID), the client secret (application password).<\/li>\n
        5. Add the Authorization URL is the following:\n
          https:\/\/login.microsoftonline.com\/common\/oauth2\/v2.0\/authorize<\/pre>\n<\/li>\n
        6. Also, include the Access token URL is the following:\n
          https:\/\/login.microsoftonline.com\/common\/oauth2\/v2.0\/token<\/pre>\n<\/li>\n
        7. The following permissions were applied in the example: User.Read\n
          offline_access\r\nSites.ReadWrite.all\r\nSites.Manage.All\r\nSites.FullControl.All<\/pre>\n
          NOTE:<\/strong>\u00a0It is advised to include offline_access<\/strong> scope in order to get a Refresh Token and to be free of any token restrictions.<\/div><\/div><\/li>\n
        8. Once that the properties are entered, press the Generate token button:\n
          \"oauth<\/a>
          SSIS OAuth with REST API with SharePoint in SSIS<\/p><\/div><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
          How to call SharePoint API in SSIS<\/h3>\n
            \n
          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:\n
          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:\n
            https:\/\/graph.microsoft.com\/v1.0\/sites\/root\/lists<\/pre>\n<\/li>\n
          3. Also, press Test request\/Response button to get the results of the query:\n
          4. The response will be in JSON format:\n
          5. Also, it is possible to store the results in a file or a variable:\n
          6. In Addition, you can run the following query to enumerate all the sites:\n
            https:\/\/graph.microsoft.com\/beta\/sites?select=siteCollection,webUrl&filter=siteCollection\/root%20ne%20null<\/pre>\n<\/li>\n
          7. Also, to show the information on a specific site, the following query will be used:\n
            https:\/\/graph.microsoft.com\/v1.0\/sites\/site-id<\/pre>\n<\/li>\n
          8. For example, if you site id is zappysys.sharepoint.com the URL would be the following:\n
            https:\/\/graph.microsoft.com\/v1.0\/sites\/zappysys.sharepoint.com<\/pre>\n<\/li>\n
          9. To enumerate sites, the following example can be useful:\n
            https:\/\/graph.microsoft.com\/beta\/sites?select=siteCollection,webUrl&filter=siteCollection\/root%20ne%20null<\/pre>\n<\/li>\n
          10. Also, to enumerate lists, you can use the following URL:\n
            https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/lists<\/pre>\n<\/li>\n<\/ol>\n
            How to read SharePoint lists in SSIS using REST API<\/h3>\n
            We will learn how to show SharePoint lists using rest API.<\/p>\n
            In addition, you can get the list ids using this query:<\/p>\n
            https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/list\r\n<\/pre>\n
            \"SSIS<\/a>
            SharePoint list API<\/p><\/div>\n
            How to read data from a Sharepoint List<\/h3>\n
              \n
            1. To get the list of ithems form a list use the following query:\n
              https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/lists\/mylist\/items\/<\/pre>\n<\/li>\n
            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<\/a>.<\/li>\n<\/ol>\n
              https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/lists\/fcf5fa2b-ceeb-4c9b-80e5-7bedd455fc9b<\/pre>\n
              \"<\/a>
              SSIS Sharepoint metadata list<\/p><\/div>\n
              How to create a SharePoint list item in SSIS\u00a0using REST API (POST Method)<\/h3>\n
              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.<\/p>\n
                \n
              1. First, in SSDT, use the REST API Task used previously and specify the following URL:\n
                https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/lists\/mynewlist\/items\/<\/pre>\n
                Where zappys.sharepoint.com is the site id and mynewlist is a new list created. Also, in the HTTP Request Method, use the POST method.<\/li>\n
              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:\n
                {\r\n\"fields\": {\r\n\"Title\": \"Widget\"\r\n}\r\n}<\/pre>\n
                \"Post<\/a>
                SharePoint Post REST API<\/p><\/div><\/li>\n
              3. Finally, if everything is successful, you will be able to see the new item created:\n
                \"sharepoint<\/a>
                SSIS SharePoint list created with a POST<\/p><\/div><\/li>\n<\/ol>\n
                How to export SharePoint API response results to a file<\/h3>\n
                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.
                \nIn this example, we will export data from the JSON Source to a JSON file using the JSON Destination Task.<\/p>\n
                  \n
                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:\n
                2. Secondly, in the Data Flow tab, drag and drop the JSON Source, the JSON Destination and join them:\n
                3. In addition, in the JSON Source, we will check the option Use Credentials and select the OAuth connection created and used before.<\/li>\n
                4. Enter Path or Web URL, we will use the following URL:\n
                  https:\/\/graph.microsoft.com\/beta\/sites\/zappysys.sharepoint.com\/lists<\/pre>\n<\/li>\n
                5. Also, We will use the following filter:\n
                  $.value[*].list<\/pre>\n
                  \"<\/a>
                  SSIS JSON Source REST API with SharePoint in SSIS<\/p><\/div><\/li>\n
                6. In addition, go to JSON Destination task, select mappings and right click on add elements:\n
                7. Also, select the attributes:\n
                8. Finally, run the package to export the data.<\/li>\n<\/ol>\n
                  Conclusion about REST API with SharePoint in SSIS<\/h2>\n
                  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.<\/a><\/p>\n
                  References<\/h2>\n