Online Data Provider: Microsoft SharePoint

Requirements

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

mceclip0.png

 

Register app in SharePoint

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

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.

mceclip2.png

 

Now, go to the Permissions page:

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

mceclip4.png

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:

mceclip7.png

 

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:

mceclip9.png

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:

mceclip8.png

 

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:

mceclip10.png

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:

mceclip11.png

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:

mceclip12.png

 

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)

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.