Azure Data Factory - Process One Array Field on Row as a string

Question

I created an Azure Data Factory pipeline that uses a Rest data source to pull data from a Rest API and copy it to an Azure SQL database. Each row in the Rest data source contains approx. 8 fields but one of those fields contains an array of values. I'm using a Copy Data task. How do I get all values from that field to map into 1 of my database fields, possibly as a string? I've tried clicking on "Collection Reference" for that field but if the array field has 5 values, it creates 5 different records in my SQL table for the one source row. I looked into using the Data Flow mapping task instead, but that one doesn't seem to support a Rest API dataset as a data source.

Please help.

Answer 1

Hello @BizLight-9871 and welcome to Microsoft Q&A.

There is a feature you overlooked, "Map complex values to string". This option found in the Mapping tab does exactly what you are asking for. The "collectionReference" is almost opposite what you want.

CollectionReference is for when you have heirarchical data where some details, common to all records, are stored at a higher level than the individual records. CollectionReference is for iterating over the inner records as you have found.

See below image for where to find "Map complex values to string".

Answer 2

Thanks - I was able to get it to work by switching to Advanced Editor mode and changing the default field mapping from [field][value][0]['label'] to [field][value] to get the full array and then YES, I checked "map complex values to string".