PROBLEM
You want to insert or update multiple values in a Person data-type column using ZappySys SharePoint Online Connector using SSIS in a Microsoft SharePoint Online List:
There is no straightforward way to accomplish this in the API Destination, and some additional configuration must be done. Let’s not waste time and proceed to the solution!
SOLUTION
Setup the Data Source component containing Person IDs
The source column, let’s name it ManyPersonsLookupId, must contain JSON arrays containing multiple Person IDs you want to insert or update. It has to be a string (DT_STR or DT_WSTR), e.g.:
Two JSON arrays containing Person IDs:
[11, 22]
[11, 22, 33]
NOTE: You don’t need to surround JSON arrays with double quotes, unless you are using ZappySys CSV File Source as a source component
Add the column containing OData type
You must also have another column specifying the Odata Type. Simply add a Derived Column in the Data Flow Task and add one additional column, “OdataType”, with this static text as the Expression:
Collection(Edm.Int32)
NOTE: You have to repeat the above procedure for each column you are trying to insert or update
Add an External Column in API Destination
Create one additional External Column for ApiDestinationInput in the API Destination component. Proceed by right-clicking the API Destination and selecting the Show Advanced Editor... menu item in the context menu:
Then add the new column:
Use this value for the column name:
fields.ManyPersonsLookupId@odata\u002Etype
It has to follow this format if your column name is different than “ManyPersons”:
fields.[ColumnName]LookupId@odata\u002Etype
Set the DataType to DT_WSTR or DT_STR.
NOTE: You have to repeat the above procedure for each column you are trying to insert or update
Map the columns
Finally, map the columns in the API Destination:
