EndPoint List Files
Name
list_files
Description
No description available [API reference]
Parameters
Output Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description |
---|---|---|---|---|---|
id |
DT_WSTR
|
nvarchar(500)
|
500 | False |
|
name |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
size |
DT_I8
|
bigint
|
False |
||
createdDateTime |
DT_DBTIMESTAMP
|
datetime
|
False |
||
lastModifiedDateTime |
DT_DBTIMESTAMP
|
datetime
|
False |
||
webUrl |
DT_WSTR
|
nvarchar(1500)
|
1500 | False |
|
webUrlPath |
DT_WSTR
|
nvarchar(1500)
|
1500 | False |
|
createdBy_user_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
createdBy_user_email |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
createdBy_user_displayName |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
createdBy_application_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
createdBy_application_displayName |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
lastModifiedBy_user_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
lastModifiedBy_user_email |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
lastModifiedBy_user_displayName |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
lastModifiedBy_application_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
lastModifiedBy_application_displayName |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
parentReference_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
parentReference_driveId |
DT_WSTR
|
nvarchar(400)
|
400 | False |
|
parentReference_driveType |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
parentReference_name |
DT_WSTR
|
nvarchar(200)
|
200 | False |
This is only returned when calling lookup or /children call (e.g. /items/root:/[folder-name] OR /items/[item-id] ) |
parentReference_siteId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
parentReference_path |
DT_WSTR
|
nvarchar(1000)
|
1000 | False |
This is only returned when calling lookup or /children call (e.g. /items/root:/[folder-name] OR /items/[item-id] ) |
fileSystemInfo_createdDateTime |
DT_DBTIMESTAMP
|
datetime
|
False |
||
fileSystemInfo_lastModifiedDateTime |
DT_DBTIMESTAMP
|
datetime
|
False |
||
folder_childCount |
DT_I8
|
bigint
|
False |
||
file_mimeType |
DT_WSTR
|
nvarchar(300)
|
300 | False |
|
file_quickXorHash |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
file_sha1Hash |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
file_sha256Hash |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
shared_scope |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
remoteItem_parentReference_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_parentReference_driveId |
DT_WSTR
|
nvarchar(400)
|
400 | False |
|
remoteItem_parentReference_driveType |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
remoteItem_parentReference_name |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_parentReference_siteId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_parentReference_path |
DT_WSTR
|
nvarchar(1000)
|
1000 | False |
|
remoteItem_shared_scope |
DT_WSTR
|
nvarchar(100)
|
100 | False |
|
remoteItem_shared_sharedDateTime |
DT_DBTIMESTAMP
|
datetime
|
False |
||
remoteItem_sharedBy_user_id |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharedBy_user_email |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
remoteItem_sharedBy_user_displayName |
DT_WSTR
|
nvarchar(150)
|
150 | False |
|
remoteItem_sharepointIds_listId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_listItemId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_listItemUniqueId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_siteId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_siteUrl |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_tenantId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
remoteItem_sharepointIds_webId |
DT_WSTR
|
nvarchar(200)
|
200 | False |
|
id_path |
DT_WSTR
|
nvarchar(200)
|
200 | False |
Input Columns
Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | 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 component to read data or in API Destination component to read/write data:
List Files using API Source

List Files using API Destination

ODBC application
Use these SQL queries in your ODBC application data source:
List files from SharePoint Document Library
SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.
List files in a folder path (Recursive)
Use list_files endpoint to list files recursively (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
--,SearchPath='/root' --default
--,SearchPath='/root:/myfolder:' --folder path
,SearchPath='/root:/myfolder/subfolder:' --folder path nested
,OrderBy='lastModifiedDateTime desc'
)
--You can get DriveId by selecting from 'Drives' table.
List files in a folder Id (Non-Recursive)
This examplel shles under a specific FolderId (Available in Non-recursive mode only).
SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
,SearchType='children' --must use this type if you like to list items by FolderId
,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)
--You can get DriveId by selecting from 'Drives' table.
SQL Server
Use these SQL queries in SQL Server after you create a data source in Data Gateway:
List files from SharePoint Document Library
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM list_files
WITH (DriveId=''b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'')
--You can get DriveId by selecting from ''Drives'' table.';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY];
List files in a folder path (Recursive)
Use list_files endpoint to list files recursively (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM list_files
WITH(
Filter=''$.value[*]'' --list both files and folders
--Filter=''$.value[?(@.file.mimeType != null)]'' --list only files
--Filter=''$.value[?(@.folder.childCount != null)]'' --list only folders
,DriveId=''b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E''
--,SearchPath=''/root'' --default
--,SearchPath=''/root:/myfolder:'' --folder path
,SearchPath=''/root:/myfolder/subfolder:'' --folder path nested
,OrderBy=''lastModifiedDateTime desc''
)
--You can get DriveId by selecting from ''Drives'' table.';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY];
List files in a folder Id (Non-Recursive)
This examplel shles under a specific FolderId (Available in Non-recursive mode only).
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM list_files
WITH(
Filter=''$.value[*]'' --list both files and folders
--Filter=''$.value[?(@.file.mimeType != null)]'' --list only files
--Filter=''$.value[?(@.folder.childCount != null)]'' --list only folders
,DriveId=''b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E''
,SearchType=''children'' --must use this type if you like to list items by FolderId
,SearchPath=''/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX''
)
--You can get DriveId by selecting from ''Drives'' table.';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_SHAREPOINT_ONLINE_IN_DATA_GATEWAY];