SSIS SharePoint Online Connector
If you are new with SSIS and ZappySys SSIS PowerPack then check below links to get started.
|
See also
|
Video Tutorial - Read 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 SSIS PowerPack for
SharePoint Online integration in SSIS - How to configure connection for
SharePoint Online - How to Read data from
SharePoint Online - Features about SSIS API Source
- Using
SharePoint Online Connector in SSIS
Step-By-Step: How to read from SharePoint Online in SSIS (Export data)
In this section we will learn how to configure and use SharePoint Online Connector in the API Source to extract data from the SharePoint Online.
-
Prerequisite: you must have downloaded and installed SSIS PowerPack. Check Getting started section for more information.
-
Begin with opening Visual Studio and creating new SSIS package Project.
-
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 drop 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.
Steps to get SharePoint Online Credentials
Firstly, login into Azure Portal and there create an OAuth application:
- 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.
- In nearby textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth
- Use this Redirect URI in Redirect URL grid row.
- Copy Application (client) ID and paste it into API Connection Manager configuration grid in Client ID row.
- Then click on Endpoints link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
- https://login.microsoftonline.com/common/oauth2/v2.0/authorize
- 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.
- Use these scopes in Scopes field: offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile Sites.Read.All Sites.ReadWrite.All User.Read email
- Click Generate Token to generate tokens.
- That's it!
User Credentials [OAuth]https://graph.microsoft.com/v1.0Required 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 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... -
Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.
API Source - SharePoint OnlineSharePoint 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. In a few clicks we configured the call to SharePoint Online API using ZappySys SharePoint Online Connector
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
Step-By-Step: How to write or lookup 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.
-
Prerequisite: you must have downloaded and installed SSIS PowerPack. Check Getting started section for more information.
-
Firstly, open Visual Studio and create a new SSIS Package Project.
-
Now, drag and drop SSIS Data Flow Task from SSIS Toolbox.
-
Double click on the Data Flow Task to see Data Flow Designer view.
-
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 OnlineSharePoint 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.
Steps to get SharePoint Online Credentials
Firstly, login into Azure Portal and there create an OAuth application:
- 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.
- In nearby textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth
- Use this Redirect URI in Redirect URL grid row.
- Copy Application (client) ID and paste it into API Connection Manager configuration grid in Client ID row.
- Then click on Endpoints link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
- https://login.microsoftonline.com/common/oauth2/v2.0/authorize
- 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.
- Use these scopes in Scopes field: offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile Sites.Read.All Sites.ReadWrite.All User.Read email
- Click Generate Token to generate tokens.
- That's it!
User Credentials [OAuth]https://graph.microsoft.com/v1.0Required 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 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... -
Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - SharePoint OnlineSharePoint 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 the desired columns:
API Destination - SharePoint OnlineSharePoint 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
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).Actions supported by SharePoint Online Connector
SharePoint Online Connectors 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.Parameter | Description |
---|---|
SiteId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
Parameter | Description |
---|---|
SiteId |
|
Parameter | Description |
---|---|
SiteId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
Expand |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId |
|
ListId |
|
ListItemId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
FileId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
FileId |
|
SheetId |
|
Range |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
FileId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
FileId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
FileId |
|
Parameter | Description |
---|---|
SiteId |
|
DriveId |
|
DiskFilePath |
|
SharePointFilePath |
|
Parameter | Description |
---|---|
Url |
|
Body |
|
IsMultiPart |
|
Filter |
|
Headers |
|
Other App Integration scenarios for SharePoint Online
Other API Connectors for SSIS
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 realtime 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