Write data in SharePoint Online using SharePoint Online Connector in SSIS

In this section we will learn how to configure and use SharePoint Online Connector in the API Destination to write data in the SharePoint Online.

Video Tutorial - Write or lookup data to SharePoint Online using SSIS

This video covers following and more so watch carefully. After watching this video follow the steps described in this article.

How to download SSIS PowerPack for SharePoint Online integration in SSIS
How to configure connection for SharePoint Online
How to Write or lookup data to SharePoint Online
Features about SSIS API Destination
Using SharePoint Online Connector in SSIS

Watch this video on YouTube

How to write or lookup data to SharePoint Online in SSIS (Import data)

In upper section we learned how to read data, now in this section we will learn how to configure SharePoint Online in the API Source to Post data to the SharePoint Online.

Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
Select New Connection to create a new connection:

API Destination - 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!
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 Help..]

Steps to get 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

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	Fill in the parameter...
ReturnUrl	Fill in the parameter...
Login Prompt Option	Fill in the parameter...
RetryMode	Fill in the parameter...
RetryStatusCodeList	Fill in the parameter...
RetryCountMax	Fill in the parameter...
RetryMultiplyWaitTime	Fill in the parameter...
Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)	Fill in the parameter...
Extra Headers (e.g. Header1:AAA\|\|Header2:BBB)	Fill in the parameter...

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 Help..]

Steps to get 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

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	Fill in the parameter...
RetryMode	Fill in the parameter...
RetryStatusCodeList	Fill in the parameter...
RetryCountMax	Fill in the parameter...
RetryMultiplyWaitTime	Fill in the parameter...
Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)	Fill in the parameter...
Extra Headers (e.g. Header1:AAA\|\|Header2:BBB)	Fill in the parameter...

Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.

API Destination - 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!
Finally, map the desired columns:

API Destination - 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 successfully configured the POST API Call. In a few clicks we configured the SharePoint Online API call using ZappySys SharePoint Online Connector