Read data from SharePoint Online in SSIS (Export data)

In this section we will learn how to configure and use SharePoint Online Connector in API Source to extract data from SharePoint Online.

Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.
In the new SSIS project screen you will find the following:
1. SSIS ToolBox on left side bar
2. Solution Explorer and Property Window on right bar
3. Control flow, data flow, event Handlers, Package Explorer in tab windows
4. Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
Select New Connection to create a new connection:
Use a preinstalled SharePoint Online Connector from Popular Connector List or press Search Online radio button to download SharePoint Online Connector. Once downloaded simply use it in the configuration:

SharePoint Online

Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

User Credentials [OAuth]

OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-register-app-v2. [API reference]

Steps how to get and use SharePoint Online credentials

Firstly, login into Azure Portal and there create an OAuth application:

Login to Azure Portal:
- Navigate to the Azure Portal and log in using your credentials.
Access Azure Active Directory:
- In the left-hand menu, click on Azure Active Directory.
Register a New Application:
- Go to App registrations and click on New registration.
- Application Name: Enter a name for your application.
- Supported Account Types: Choose the account types your app will support. For example, select Accounts in this organizational directory only if you need access to data in your organization only.
- Redirect URI:
  - Set the type to Web.
  - In the textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as the Redirect URI or any other valid redirect URL, e.g., https://zappysys.com/oauth.
  - Use this Redirect URI in the Redirect URL grid row.
Save Client ID:
- After registering the app, copy the Application (client) ID and paste it into the Client ID field in the API Connection Manager configuration.
Set Authorization & Token URLs:
- Click on the Endpoints link in the App registration overview.
- Authorization URL: Copy the OAuth 2.0 authorization endpoint (v2) URL (e.g., https://login.microsoftonline.com/{your-tenant-id}/oauth2/v2.0/authorize) and paste it into the Authorization URL field in the configuration grid.
- Token URL: Copy the OAuth 2.0 token endpoint (v2) URL (e.g., https://login.microsoftonline.com/{your-tenant-id}/oauth2/v2.0/token) and paste it into the Token URL field.
Create a Client Secret:
- In the Certificates & secrets tab, click New client secret.
- Set an expiration period for the secret.
- Copy the generated client secret and paste it into the Client Secret field in the API Connection Manager configuration.
Configure API Permissions:
- Go to the API Permissions section.
- Click on Add a permission, select Microsoft Graph, and choose Delegated Permissions.
- Add the required permissions:
  - offline_access
  - Files.Read
  - Files.Read.All
  - Files.ReadWrite
  - Files.ReadWrite.All
  - openid
  - profile
  - Sites.Read.All
  - Sites.ReadWrite.All
  - User.Read
  - email
- Grant Admin Consent for the permissions that require it.
Generate Tokens:
- Use the Generate Token feature in the API Connection Manager to generate authentication tokens.
Use a Generic Account for Automation:
NOTE: If you are planning to use your current data connection/token for automated processes, we recommend that you use a generic account for token generation when the login box appears (e.g. sales_automation@mycompany.com instead of bob_smith@mycompany.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will fail. Another potentially unwanted effect of using a personal token is incorrect logging; the API calls (e.g. Read, Edit, Delete, Upload) made with that token will record the specific user as performing the calls instead of an automated process.
That's it!

For more information you can read this link https://docs.microsoft.com/en-us/graph/auth-register-app-v2 Also https://docs.microsoft.com/en-us/graph/auth-register-app-v2
Configuring authentication parameters

SharePoint Online

User Credentials [OAuth]

https://graph.microsoft.com/v1.0

Required Parameters
Authorization URL	Fill-in the parameter...
Token URL	Fill-in the parameter...
Client ID	Fill-in the parameter...
Scope	Fill-in the parameter...
SiteId (select after pressing 'Generate Token')	Fill-in the parameter...
Optional Parameters
Client Secret
ReturnUrl
Login Prompt Option
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429\|503
RetryCountMax	5
RetryMultiplyWaitTime	True
Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
Extra Headers (e.g. Header1:AAA\|\|Header2:BBB)

Application Credentials [OAuth]

OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-v2-service [API reference]

Steps how to get and use SharePoint Online credentials

Firstly, login into Azure Portal and there create an OAuth application:

Step-1: Create OAuth App

Go to Azure Portal and login there.
Then go to Azure Active Directory.
On the left side click menu item App registrations
Then proceed with clicking New registration.
Enter a name for your application.
Select one of Accounts in this organizational directory only as supported account type to access data in your company.
In Redirect URI section leave option selected at Web.
Copy Application (client) ID and paste it into API Connection Manager configuration grid in Client ID row.

Then click on Endpoints link and copy second, Token, URL to Token URL field in configuration grid. Usually they look similar to these:

https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
https://login.microsoftonline.com/common/oauth2/v2.0/token

Close "Endpoints" and create a Client Secret by clicking Certificates & secrets.
Proceed by clicking New client secret and setting expiration period. Copy the client secret and paste it into configuration grid in Client Secret row.
Go to API Permissions section and add Microsoft Graph permissions for SharePoint and Drive, e.g. Sites.Selected, Sites.Read.All, Sites.ReadWrite.All, Files.Read.All, Files.ReadWrite.All.
Finally, Grant admin consent for your domain for your permissions.

Step-2: Register OAuth App in SharePoint Admin Portal

After we create OAuth App we need to add grant SharePoint access to that App using Admin Portal. Make sure you have admin permission to acces Portal. For detailed steps you may refer to this link. Here are basic steps you can follow.

https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web

Enter App Id and other information

Enter XML snippet which describes which permission you want to grant. Here is an example permission (App with FullControl / Read / Write)

<AppPermissionRequests AllowAppOnlyPolicy="true">
  <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests>

Click Add to grant the permission. That's it!

NOTE: After adding App in SharePoint if you ever remove the permission then you can visit the following URL and delete the App.

https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web

Configuring authentication parameters

SharePoint Online

Application Credentials [OAuth]

https://graph.microsoft.com/v1.0

Required Parameters
Token URL	Fill-in the parameter...
Client ID	Fill-in the parameter...
Client Secret	Fill-in the parameter...
SiteId	Fill-in the parameter...
Optional Parameters
Scope	https://graph.microsoft.com/.default
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429\|503
RetryCountMax	5
RetryMultiplyWaitTime	True
Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
Extra Headers (e.g. Header1:AAA\|\|Header2:BBB)

Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.

API Source - SharePoint Online

SharePoint Connector can be used to integrate SharePoint and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete SharePoint data in a few clicks!
That's it! We are done! Just in a few clicks we configured the call to SharePoint Online using SharePoint Online Connector.

You can load the source data into your desired destination using the Upsert Destination, which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV, Excel, Azure Table, Salesforce, and more. You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)