Reference

Endpoint Read List Items (Dynamic Columns)

Name

get_list_items_dynamic

Description

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)

Parameters

Parameter Required Options
Name: ListId

Label: ListId

Specify a list id to search items for 		YES
Name: SiteId

Label: SiteId (Re-Select ListId after you change this)

Specify a site
Name: Filter

Label: 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')
Name: expand

Label: Expand (Output Fields)

By default lookup fields are returned as (null). Select fields which you want to process (to speedup processing check only those fields which are (null) in the output)

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.
If the column you are looking for is missing, consider customizing SharePoint Online Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use SharePoint Online Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - SharePoint Online
SharePoint Connector can be used to read, write data in SharePoint Online List / Document Library, perform file operations such as upload, download, create, move, delete, rename in a few clicks!
SharePoint Online
Read List Items (Dynamic Columns)
Required Parameters
ListId Fill-in the parameter...
Optional Parameters
SiteId (Re-Select ListId after you change this)
Filter
SSIS API Source - Read from table or endpoint

API Destination

API Destination - SharePoint Online
SharePoint Connector can be used to read, write data in SharePoint Online List / Document Library, perform file operations such as upload, download, create, move, delete, rename in a few clicks!
SharePoint Online
Read List Items (Dynamic Columns)
Required Parameters
ListId Fill-in the parameter...
Optional Parameters
SiteId (Re-Select ListId after you change this)
Filter
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get list items - dynamic metadata (SharePoint Document Library)

Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person) 

SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get list items - dynamic metadata (SharePoint Document Library)

Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person) 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_list_items_dynamic
WITH (ListId=''14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'')
--You can get ListId by selecting from ''list_lists'' endpoint.';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];