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: MetaDetectionOrder

Label: Metadata Mode (Change if columns missing or NULL) **SLOW**

Only change if expected columns are missing (e.g. SLA or nested custom fields). Default uses predefined columns (fast). MergeStaticDynamic scans sample data to infer types and merge with static metadata—slower but exposes nested/document columns; use META='@OverrideMode:1' in SQL to add specific dynamic fields.
Option Value
Default Default
Static Static
MergeStaticDynamic MergeStaticDynamic
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
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
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
Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
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:

Read items from a list (dynamic columns)

<p>Same as reading list items with <code>ListId</code>, but uses <code>get_list_items_dynamic</code> so column types are inferred from sample rows. Use when <code>get_list_items</code> leaves complex fields null or wrong (e.g. Lookup, Person, Location) or types do not match.</p> 

SELECT * FROM get_list_items_dynamic
WITH (
	ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
	)
--Obtain ListId from the Lists table or list_lists (e.g. SELECT id, name FROM Lists).

SQL Server

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

Read items from a list (dynamic columns)

<p>Same as reading list items with <code>ListId</code>, but uses <code>get_list_items_dynamic</code> so column types are inferred from sample rows. Use when <code>get_list_items</code> leaves complex fields null or wrong (e.g. Lookup, Person, Location) or types do not match.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_list_items_dynamic
WITH (
	ListId=''14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX''
	)
--Obtain ListId from the Lists table or list_lists (e.g. SELECT id, name FROM Lists).';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];