EndPoint Upload File

Name

upload_file

Description

Upload a file to the specified path [API reference]

Parameters

Parameter

Label

Required

Options

Description

DriveId

Drive Id

YES

Specify a drive

DiskFilePath

Disk File Path (e.g. c:\somefolder\file.xlsx)

YES

Specify a disk file path

SharePointFilePath

Target File Path OR Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234)

YES

Option	Value
Example #1 (Save On Root)	file.xlsx
Example #2 (Save Under Folder)	myfolder/myfile.xlsx
Example #3 (Save Under Child Folder)	myfolder/subfolder/myfile.xlsx
Example #4 (Overwrite Exising File by ID)	0Zxxxx1234

Specify target file path

ConflictBehavior

Conflict Behavior (What to do if file exists)

YES

Option	Value
fail	fail
replace	replace
rename	rename

SiteId

Site Id (Re-Select Drive Id after you change this)

Specify a site

UseSourceFileTimeStamp

Use Source File Created/Modified DateTime rather than Uploaded Time

Option	Value
Use Uploaded Time (Blank)
Use Source File Time	true

Set this property if you like to set file creation/modified date as original file. By default its set to uploaded date/time. This may not be desired if file was actually created on different date than uploaded. If you do not set then current date/time used (Upload time).

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:

Upload File using API Source

SharePoint Online

Upload File

SSIS API Source - Read from table or endpoint

Upload File using API Destination

SharePoint Online

Upload File

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Upload a file to a SharePoint Document Library

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
	 ,DiskFilePath='C:\My files\Employees\List.xls'
	 ,SharePointFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
   --,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
   --,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
   --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date   
   )
--DriveId can be retrieved by selecting from 'Drives' table.

Upload a file to SharePoint Document Library (Overwrite file if exists)

Uploads a file to a SharePoint Document Library and if file already exists then overwrite it rather than throwing error (Default is throw error)

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
     ,DiskFilePath='C:\My files\Employees\List.xls'
	 ,SharePointFilePath='List.xlsx'	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
   --,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
   --,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
     ,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
     --,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from 'list_drives' endpoint.

SQL Server

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

Upload a file to a SharePoint Document Library

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM upload_file
WITH (DriveId=''b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX''
	 ,DiskFilePath=''C:\My files\Employees\List.xls''
	 ,SharePointFilePath=''List.xlsx''	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = ''replace'')
   --,SharePointFilePath=''myfolder/List.xlsx'' --Upload under some folder
   --,SharePointFilePath=''01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB'' --Overwrite Exising File Id
   --,ConflictBehavior = ''replace'' -- fail, rename, replace (If File Exists Default=fail)
   --,UseSourceFileTimeStamp = ''true'' --By default uploaded file set Current Date/Time for Creation/Modified date   
   )
--DriveId can be retrieved by selecting from ''Drives'' table.';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];

Upload a file to SharePoint Document Library (Overwrite file if exists)

Uploads a file to a SharePoint Document Library and if file already exists then overwrite it rather than throwing error (Default is throw error)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM upload_file
WITH (DriveId=''b!GtLN726LE0eY5F2BBNi14''
     ,DiskFilePath=''C:\My files\Employees\List.xls''
	 ,SharePointFilePath=''List.xlsx''	 --Upload on Root (Create -or- Overwrite if ConflictBehavior = ''replace'')
   --,SharePointFilePath=''myfolder/List.xlsx'' --Upload under some folder
   --,SharePointFilePath=''01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB'' --Overwrite Exising File Id
     ,ConflictBehavior = ''replace'' -- fail, rename, replace (If File Exists Default=fail)
     --,UseSourceFileTimeStamp = ''true'' --By default uploaded file set Current Date/Time for Creation/Modified date
     )
--DriveId can be retrieved by selecting from ''list_drives'' endpoint.';

EXEC (@MyQuery) AT [LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY];