SharePoint Online Connector for SSIS

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!
You can use this connector to integrate SharePoint Online data inside SSIS and SQL Server. Let's take a look at the steps below to see how exactly to accomplish that.

Download Documentation

If you are new with SSIS and ZappySys SSIS PowerPack then check below links to get started.

How to get started with SSIS and SSIS PowerPack. it covers how to install necessary tools to design/debug SSIS Packages, deploy to SSIS catalog and last Schedule SSIS Package to run using SQL Agent. It will also cover how to secure SSIS package and Parameterize values (e.g. Password, Hostname, etc).
SSIS API Source Help File
SSIS API Destination Help File

Connect to SharePoint Online in other apps

Connect to SharePoint Online in SQL Server

Connect to SharePoint Online in Power BI

Connect to SharePoint Online in SSRS

Connect to SharePoint Online in Informatica

Connect to SharePoint Online in MS Access

Connect to SharePoint Online in MS Excel

Connect to SharePoint Online in SSAS

Connect to SharePoint Online in C#

Connect to SharePoint Online in Python

Connect to SharePoint Online in JAVA

Connect to SharePoint Online in Tableau

Connect to SharePoint Online in SAP Crystal Reports

Connect to SharePoint Online in Azure Data Factory (Pipeline)

Connect to SharePoint Online in Azure Data Factory (SSIS)

Connect to SharePoint Online in Talend Studio

Connect to SharePoint Online in UiPath

Connect to SharePoint Online in PowerShell

Connect to SharePoint Online in ODBC

Video Tutorial - Integrate SharePoint Online data in SSIS

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

How to download / install required driver for SharePoint Online integration in SSIS
How to configure connection for SharePoint Online
Features about API Source (Authentication / Query Language / Examples / Driver UI)
Using SharePoint Online Connection in SSIS

Watch this video on YouTube

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is downloaded and installed (download it). Check Getting started section for more information.
Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

How to 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 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 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)

Load SharePoint Online data in SQL Server using Upsert Destination (Insert or Update)

Once you read data from the desired source, now let's see how to Load SharePoint Online data in SQL Server using Upsert Destination. Upsert Destination can Merge/Synchronize data from source to target for Microsoft SQL Server, PostgreSql and Redshift. It supports very fast Bulk Upsert (Update or Insert) operation along and Bulk delete.

From the SSIS toolbox drag and drop Upsert Destination on the dataflow designer surface
Connect our Source component to Upsert Destination
Double click on Upsert Destination component to configure it.
Select the desired Microsoft SQL Server/PostgreSql/Redshift Target Connection or click NEW to create new connection. Select Target Table or click NEW to create new table based on source columns.

Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
Click on OK to save Upsert Destination settings UI.
That's it. Run the SSIS Package and it will read the data from the SharePoint Online and load the data in the SQL Server/PostgreSql/Redshift using Upsert Destination.

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

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

Advanced topics

Actions supported by SharePoint Online Connector

SharePoint Online Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.

Read Main Site

[Read more...]

List Sites

[Read more...]

List Drives

[Read more...]

Parameter	Description
SiteId	Specify a site

Read Drive

[Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive

List Folders

[Read more...]

Parameter

Description

Record Filter (Client Side)

Option	Value
All Folders	$.value[?(@.folder.childCount !=null)]
All Folders Under a Specific Folder	$.value[?(@.folder.childCount !=null && @parentReference.id=='SOME-FOLDER-ID-GOES-HERE')]
Folder Name Equals	$.value[?(@.folder.childCount !=null && @name=='SOME-FOLDER-NAME-HERE')]
Folder Id Equals	$.value[?(@.folder.childCount !=null && @id=='SOME-FOLDER-ID-GOES-HERE')]
Folder Name Contains	$.value[?(@.folder.childCount !=null && @name=~/somename$/)]
Folder Name With Regex Pattern	$.value[?(@.folder.childCount !=null && @name=~/^somefolder_\d\d\d.txt$/)]
Folder Name With Regex Pattern (Multiple)	$.value[?(@.folder.childCount !=null && @name=~/somefolderAAA\|somefolderBBB/)]

SiteId

Specify a site

DriveId

Specify a drive

Search Type (Default=Recursive)

If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).

Option	Value
Recursive	search(q='')
NonRecursive	children

Search Path (Default=Root Folder)

Option	Value
/root:	/root:
/root:/somefolder/subfolder:	/root:/somefolder/subfolder:

Order By Field(s) - Only for Recursive SearchType

Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc

Option	Value
id	id
name	name
name desc	name desc
size	size
size desc	size desc
createdDateTime	createdDateTime
createdDateTime desc	createdDateTime desc
lastModifiedDateTime	lastModifiedDateTime
lastModifiedDateTime desc	lastModifiedDateTime desc

List Files

[Read more...]

Parameter

Description

Record Filter (Client Side)

Option	Value
All Files	$.value[?(@.file.mimeType != null)]
All Files Under a Specific Folder	$.value[?(@.file.mimeType != null && @parentReference.id=='SOME-FOLDER-ID-GOES-HERE')]
All Folders	$.value[?(@.folder.childCount != null)]
All Files and Folders	$.value[*]
File Name Equals	$.value[?(@.file.mimeType != null && @name=='somefile.txt')]
File Id Equals	$.value[?(@.file.mimeType != null && @id=='SOME-FILE-ID-GOES-HERE')]
File Name Contains	$.value[?(@.file.mimeType != null && @name=~/somename$/)]
File Name With Regex Pattern	$.value[?(@.file.mimeType != null && @name=~/^somefile_\d\d\d.txt$/)]
File Name With Regex Pattern (Multiple)	$.value[?(@.file.mimeType != null && @name=~/somefile.txt\|somefile.json/)]

SiteId

Specify a site

DriveId

Specify a drive

Search Type (Default=Recursive)

Option	Value
Recursive	search(q='')
NonRecursive	children

Search Path (Default=Root Folder)

Option	Value
/root:	/root:
/root:/somefolder/subfolder:	/root:/somefolder/subfolder:

Order By Field(s) - Only for Recursive SearchType

Option	Value
id	id
name	name
name desc	name desc
size	size
size desc	size desc
createdDateTime	createdDateTime
createdDateTime desc	createdDateTime desc
lastModifiedDateTime	lastModifiedDateTime
lastModifiedDateTime desc	lastModifiedDateTime desc

List Excel Files

[Read more...]

Parameter

Description

SiteId

Specify a site

DriveId

Specify a drive

Record Filter (Client Side)

Option	Value
All Files	$.value[?(@.file.mimeType != null)]
All Files Under a Specific Folder	$.value[?(@.file.mimeType != null && @parentReference.id=='SOME-FOLDER-ID-GOES-HERE')]
All Folders	$.value[?(@.folder.childCount != null)]
File Name Equals	$.value[?(@.file.mimeType != null && @name=='somefile.txt')]
File Id Equals	$.value[?(@.file.mimeType != null && @id=='SOME-FILE-ID-GOES-HERE')]
File Name Contains	$.value[?(@.file.mimeType != null && @name=~/somename$/)]
File Name With Regex Pattern	$.value[?(@.file.mimeType != null && @name=~/^somefile_\d\d\d.txt$/)]
File Name With Regex Pattern (Multiple)	$.value[?(@.file.mimeType != null && @name=~/somefile.txt\|somefile.json/)]

Search Type (Default=Recursive)

Option	Value
Recursive	search(q='.xlsx')
NonRecursive	children

Search Path (Default=Root Folder)

Option	Value
/root:	/root:
/root:/somefolder/subfolder:	/root:/somefolder/subfolder:

Order By Field(s) - Only for Recursive SearchType

Option	Value
id	id
name	name
name desc	name desc
size	size
size desc	size desc
createdDateTime	createdDateTime
createdDateTime desc	createdDateTime desc
lastModifiedDateTime	lastModifiedDateTime
lastModifiedDateTime desc	lastModifiedDateTime desc

List SharePoint Lists

[Read more...]

Parameter	Description
SiteId	Specify a site

List SharePoint System Lists

[Read more...]

Parameter	Description
SiteId	Specify a site

Read List Fields

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for
Expand

Read List

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for

Read List Items

[Read more...]

Parameter

Description

SiteId

Specify a site

ListId

Specify a list id to search items for

Filter

To filter data on a field, you will first need to index it: https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0

Option	Value
None
Equal Search	fields/SomeColumn eq 'SomeValue'
Substring Search	substringof(fields/SomeField,'abc')
Starts With	startswith(fields/SomeField, 'abc')

Read List Items (Dynamic Columns)

Use this endpoint if you face issue with NULl values in some columns. This endpoint scans metadata from few sample rows and decides datatypes. Use this if other endpoing showing null values for Complex datatypes (e.g. Lookup, Location, Person) [Read more...]

Parameter

Description

SiteId

Specify a site

ListId

Specify a list id to search items for

Filter

To filter data on a field, you will first need to index it: https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0

Option	Value
None
Equal Search	fields/SomeColumn eq 'SomeValue'
Substring Search	substringof(fields/SomeField,'abc')
Starts With	startswith(fields/SomeField, 'abc')

Read List Item (By ID)

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for
ListItemId	Specify a list item id (Record ID)

Create List Item

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for

Update List Item

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for
ListItemId	Specify a list item id

Delete List Item

[Read more...]

Parameter	Description
SiteId	Specify a site
ListId	Specify a list id to search items for
ListItemId	Specify a list item id

List Excel Worksheets (Tabs)

[Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id to search items in
FileId	Specify a drive Id to search items in

Read Excel Worksheet (Auto Detect Range)

Use this endpoint to read data from a Sheet without supplying address range (e.g. A1:D100). This endpoint auto detects address range based on used cells in a sheet. [Read more...]

Parameter

Description

SiteId

Specify a site

DriveId

Specify a drive Id

FileId

Specify a file id

SheetId

Specify a sheet name

AutoDetectByValue

Auto detect cell range based on values only and do not consider cell formatting (for empty values) to detect the range.

Option	Value
true	true
false	false

Read Excel Worksheet (Specific Range)

Use this endpoint to read data from excel worksheets with address range e.g. A1:D100. If you want dynamic range or unsure about end row then use the other endpoint with Auto Detect feature [Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id
FileId	Specify a file id
SheetId	Specify a sheet name
Range

Download File as a column field

Dowloads a File as a column field [Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id
FileId	Specify a file id

Download File to disk

Dowloads a File to Local Disk [Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id
FileId	Specify a file id

Delete File from Sharepoint Site

Delete a File [Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id
FileId	Specify a file id

Upload File

Uploads a File [Read more...]

Parameter	Description
SiteId	Specify a site
DriveId	Specify a drive Id
DiskFilePath	Specify a disk file path
SharePointFilePath	Specify target file path

Generic Request

This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL. [Read more...]

Parameter	Description
Url	API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body	Request Body content goes here
IsMultiPart	Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter	Enter filter to extract array from response. Example: $.rows[] --OR-- $.customers[].orders[*]. Check your response document and find out hierarchy you like to extract
Headers	Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair

Conclusion

In this article we discussed how to connect to SharePoint Online in SSIS and integrate data without any coding. Click here to Download SharePoint Online Connector for SSIS and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).

Download SharePoint Online Connector for SSIS Documentation

More integrations

Other application integration scenarios for SharePoint Online

SQL Server

Power BI

SSRS

Informatica

MS Access

MS Excel

SSAS

Python

JAVA

Tableau

SAP Crystal Reports

Azure Data Factory (Pipeline)

Azure Data Factory (SSIS)

Talend Studio

UiPath

PowerShell

ODBC

Other connectors for SSIS

Amazon Ads

Amazon MWS

Amazon Selling Partner (SP-API)

Asana

Azure DevOps

Cosmos DB

DropBox

FastSpring

Google Drive

Hubspot

Jira

Mailchimp

ManagedEngine ServiceDesk Plus (Zoho)

Nativo

OData

OneDrive

Outlook Mail (Office 365)

Power BI

ServiceNow

SharePoint Online

Shopify

Smartsheet

Stripe

YouTube

Zendesk

Zoho CRM

Zoho SalesIQ

Zoom

REST API

JSON

JSON File

XML

SOAP

XML File

JDBC-ODBC Bridge

Salesforce

CSV

Amazon S3 JSON File

Amazon S3 XML File

Amazon S3 CSV File

Azure Blob JSON File

Azure Blob XML File

Azure Blob CSV File

FTP/SFTP JSON File

FTP/SFTP XML File

FTP/SFTP CSV File

Build your own Custom API Connector

Download SharePoint Online Connector for SSIS Documentation

Common Searches:

How to connect SharePoint Online in SSIS?
How to get SharePoint Online data in SSIS?
How to read SharePoint Online data in SSIS?
How to load SharePoint Online data in SSIS?
How to import SharePoint Online data in SSIS?
How to pull SharePoint Online data in SSIS?
How to push data to SharePoint Online in SSIS?
How to write data to SharePoint Online in SSIS?
How to POST data to SharePoint Online in SSIS?
Call SharePoint Online API in SSIS
Consume SharePoint Online API in SSIS
SharePoint Online SSIS Automate
SharePoint Online SSIS Integration
Integration SharePoint Online in SSIS
Consume real-time SharePoint Online data in SSIS
Consume real-time SharePoint Online API data in SSIS
SharePoint Online ODBC Driver | ODBC Driver for SharePoint Online | ODBC SharePoint Online Driver | SSIS SharePoint Online Source | SSIS SharePoint Online Destination
Connect SharePoint Online in SSIS
Load SharePoint Online in SSIS
Load SharePoint Online data in SSIS
Read SharePoint Online data in SSIS
SharePoint Online API Call in SSIS