Online Data Provider: Microsoft SharePoint

[Version: 2022 - Winter]
[Build: 22.11.29002] 

Requirements

• Access to your organization's SharePoint Portal
• A TARGIT license for the 'SharePoint' Online Data Provider:

Register app in SharePoint

Log in to you SharePoint portal's App registration page:

• https://[YourOrganization].sharepoint.com/sites/[SiteName]/_layouts/15/appregnew.aspx

Generate the Client ID and the Client Secret and copy these to e.g., Notepad.

Note: The App Domain and the Redirect URI is not used, but need to be filled in anyway.

Now, go to the Permissions page:

• https://[YourOrganization].sharepoint.com/sites/[SiteName]/_layouts/15/appinv.aspx

Paste the App ID from your Notepad copy and click the 'Lookup' button. This will auto-fill most of the other fields.

The code used for the Permission Requests XML:

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

Note: If your security policy requires a different authorization method, please see this article: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins

Once you have created the Permission, you will asked to trust the App:

SharePoint Tenant

At some point you will need to know the Tenant of your SharePoint Online. One way of looking up the Tenant ID is by going to the Site Collection App Permissions page:

• https://[YourOrganization].sharepoint.com/sites/[SiteName]/_layouts/15/appprincipals.aspx

Copy the Tenant ID and add it to your Notepad notes.

Load data with TARGIT InMemory ETL

Create or open a project in TARGIT InMemory ETL Studio and add a new data source of the type SharePoint Online - Designer:

Fill in the Address field with the URL of your organization's SharePoint and one of your site names. Furthermore, paste in from your Notepad copies to fill in the Tenant, the Client ID and the Client Secret:

Once you connect, you can select from List, Columns and Filters.

With this new data source in the ETL project, you can now create an Import task (and a Save task) to load data from your SharePoint:

Default SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)

In the TARGIT InMemory Query Tool, running the above project should result in a table like this:

Load data with OData query

This type of the data provider will allow you to extract data using the OData URI. SQL queries is not allowed here.

OData URI conventions: https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

OData query operations in SharePoint REST requests: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests?redirectedfrom=MSDN

Examples:

https://[YourOrganization].sharepoint.com/sites/[SiteName]/_api/web/lists
https://[YourOrganization].sharepoint.com/sites/[SiteName]/_api/web/lists/GetByTitle('documents')/Items
https://[YourOrganization].sharepoint.com/sites/[SiteName]/_api/web/lists/GetByTitle('documents')/Items?$select=ID
https://[YourOrganization].sharepoint.com/sites/[SiteName]/_api/web/lists/GetByTitle('documents')/Items?$select=ID&$filter=ID> 1
https://[YourOrganization].sharepoint.com/sites/[SiteName]/_api/web/lists/GetByTitle('documents')/Fields

Default SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)

Load data with SQL

Allows to extract data using SQL queries.

Examples:

select * from [Documents](OPTIONS: depth=3)
select ID, Title, Created from [Documents](OPTIONS: depth=3)
select ID, Title, Created from [Documents]where ID =1 (OPTIONS: depth=3)
select * from sys.columns where TableName = [Documents](OPTIONS: depth=3)
select Title, EntityPropertyName, InternalName from sys.columns where TableName = [Documents](OPTIONS: depth=3)