question

ewinkiser avatar image
0 Votes"
ewinkiser asked ·

ADF Mapping Issue (NOT Mapping Data Flows) - (another urgent issue)

Hello! @nasreen-akter @MartinJaffer-MSFT @HimanshuSinha-MSFT

We have a major issue in QA Testing before we can go live; we have the following in a json data file that I am using the COPY to copy the data into a SQL Server Table. Please see the following: It was just noticed that the ADF is copying only the first item in the array below and that is all to the first row in the DB Table (see screenshot below). How can I map in ADF (we DON"T have Mapping Data Flows in the Govt Cloud yet) the following so that I can copy all 3 of these phone number types into the DB Table?
Thanks
MIke

"contactInformation": {
"phoneNumbers": [
{
"isPrimary": true,
"type": "Business",
"phoneNumber": "215/483-1234"
},
{
"isPrimary": false,
"type": "Business Cellular",
"phoneNumber": "541/281-6984"
},
{
"isPrimary": false,
"type": "Home",
"phoneNumber": "979-256-9666"
}
],

77428-image.png


azure-data-factory
image.png (26.3 KiB)
10 |1000 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.

nasreen-akter avatar image
0 Votes"
nasreen-akter answered ·

Hi @ewinkiser,

There is an option in the CopyActivity --> Collection reference. Please select phoneNumbers [] array on the dropdown. Hope this will work for you! Thanks! :)

77417-img22.jpg



img22.jpg (57.3 KiB)
· 1 ·
10 |1000 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.

Awesome! @nasreen-akter !! That worked!! Thanks so much! Mike Kiser

1 Vote 1 ·
ma01 avatar image
0 Votes"
ma01 answered ·

It depends on how you are parsing Json data in source data set of your pipeline. For above json data , please try with source data set query as below and then map to SQL Server table as target data set.

Select
ci.isPrimary
,ci.type
,ci.phoneNumber
from c
JOIN ci IN c.contactInformation.phoneNumbers


77437-image.png



image.png (35.5 KiB)
·
10 |1000 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.

ma01 avatar image
0 Votes"
ma01 answered ·

If Json is from storage account, Map the collection reference in Copy Activity.

77453-image.png





image.png (21.5 KiB)
· 1 ·
10 |1000 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.

Thanks! That did it
Mike

0 Votes 0 ·