SSIS tutorial: How to fix SharePoint error 400 and 500 related to datatypes

Tutorials SSIS Tutorials
,
1

Introduction

In this post, we will see how to fix the two most common errors related to sending bad data or missing required data when writing to the SharePoint API.

Error: (400) Bad Request - invalidRequest

During the Insert / Update (POST or PUT) operation, if you did not supply an OData Type for Multiselect Datatype, then you may see the following error.

Response Body: {“error”:{“code”:“invalidRequest”,“message”:“Invalid request”,“innerError”:{“date”:“2025-11-17T20:48:54”,“request-id”:“b30b17b0-2a98-4024-98db-5f159f59495d”,“client-request-id”:“b30b17b0-2a98-4024-98db-5f159f59495d”}}}
—> System.Net.WebException: The remote server returned an error: (400) Bad Request

How to fix in SSIS

To fix this error in SharePoint Online ODBC Driver, supply a static value for the ODataType column as below

SSIS fix SharePoint Multiselect DataType issue - 400 Bad request
SSIS fix SharePoint Multiselect DataType issue - 400 Bad request903×404 30.7 KB

SSIS SharePoint API Destination - Mapping ODataType
SSIS SharePoint API Destination - Mapping ODataType914×366 39.8 KB

How to fix in ODBC

If you are using the ZappySys API Driver for SharePoint and you get a similar error, then make sure to pass the same field value as below.

UPDATE  "Travel Requests" --list name
SET   
	 Title='MyTitle @ <<FUN_NOW>>'  
	
	--Choice Multi-Select
	, CustomChoiceMulti = '["AA", "BB"]'
	, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type 
	
	--Lookup
	--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
	, CustomLookupSingleLookupId = 1 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type 
	, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
	
	--Person 
	,CustomPersonLookupId = 11  
	
	--Person Multi-Select
	, CustomPersonMultiLookupId = '[11, 22]' 
	, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
	
Where Id=1 --List Item Id

--- Insert Example ----

INSERT INTO "Travel Requests" --list name
(
	 Title

	, CustomChoiceMulti 
	, CustomChoiceMultiODataType

	, CustomLookupSingleLookupId 
	
	--Lookup Multi-Select
	, CustomLookupMultiLookupId 
	, CustomLookupMultiLookupIdODataType
	
	--Person 
	,CustomPersonLookupId 
	
	, CustomPersonMultiLookupId 
	, CustomPersonMultiLookupIdODataType
)
VALUES(
	  'MyTitle @ <<FUN_NOW>>'  
	
	--Choice Multi-Select
	, '["AA", "BB"]'
	, 'Collection(Edm.String)' --for multi select must supply OData Type 
	
	--Lookup
	,  1 
	
	--Lookup Multi-Select
	, '[1,3]' --for multi select must supply OData Type 
	, 'Collection(Edm.Int32)'
	
	--Person 
	,11  
	
	--Person Multi-Select
	, '[11, 22]' 
	, 'Collection(Edm.Int32)'
)

Error: (500) General exception while processing

You may also get a slightly different error like this.

{
“error”: {
“code”: “generalException”,
“message”: “General exception while processing”,
“innerError”: {
“date”: “2025-11-17T21:35:29”,
“request-id”: “e8e05b88-0013-435b-8d8d-cc64930792bd”,
“client-request-id”: “e8e05b88-0013-435b-8d8d-cc64930792bd”
}
}
}

How to fix in SSIS

Make sure you do not map bad datatypes. Example: If you mapped Int32 type to a string field, then change to String → String.

Bad DataType mapping in SSIS for SharePoint - Error 500
Bad DataType mapping in SSIS for SharePoint - Error 500993×353 32.4 KB

How to fix in ODBC

For the ODBC Driver, use a string type when the target field is a string.

Bad SQL Example

INSERT INTO MyList(SomeText) VALUES(1234)  --//sending number in string field causes error, must be quoted to treat as string

Good SQL Example

INSERT INTO MyList(SomeText) VALUES('1234')  --//quote number to treat as string

Still need help?

If the issue persists, please contact our support team: