question

BizLight-9871 avatar image
0 Votes"
BizLight-9871 asked MartinJaffer-MSFT commented

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

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.

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BizLight-9871 did my suggestion help you? If it solved your issue, please mark as accepted answer, otherwise let me know how I may better assist.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

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".

92241-image.png



image.png (54.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BizLight-9871 avatar image
1 Vote"
BizLight-9871 answered MartinJaffer-MSFT commented

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".

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great! Thank you for letting us know.

0 Votes 0 ·