copy from json as source to azure database

Question

Copy from json source to azure sql dw fails to load. I am getting the below error message while loading the data to database.

I guess I have to make some changes to mappings.. not sure what exactly. Also in mapping tab I cannot use CollectionReference on "quotes" as it is object and not an array Any pointers greatly appreciated?

Sample JSON

[
        {
            "base_currency": "USD",
            "quotes": {
                "EUR": {
                    "ask": "0.86000",
                    "bid": "0.85946",
                    "date": "2020-09-27T23:59:59+00:00",
                    "high_ask": "0.86057",
                    "high_bid": "0.85983",
                    "low_ask": "0.85932",
                    "low_bid": "0.85920",
                    "midpoint": "0.85973"
                },
                "GBP": {
                    "ask": "0.78488",
                    "bid": "0.78406",
                    "date": "2020-09-27T23:59:59+00:00",
                    "high_ask": "0.78504",
                    "high_bid": "0.78416",
                    "low_ask": "0.78310",
                    "low_bid": "0.78266",
                    "midpoint": "0.78447"
                }
            }
        }
    ]

"message": "ErrorCode=SchemaMappingFailedInHierarchicalToTabularStage,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to process hierarchical to tabular stage, error message: An item with the same key has already been added.,Source=Microsoft.DataTransfer.ClientLibrary,'"

Table Schema

    [Currency] [varchar](3) NULL,
        [Date] [datetime] NULL,
        [Bid] [varchar](10) NULL,
        [MidPoint] [varchar](10) NULL,
        [Ask] [varchar](10) NULL

Mapping Details on Copy Activity

"translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "path": "$['base_currency']"
                                },
                                "sink": {
                                    "name": "Base_Currency",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['quotes']['EUR']['ask']"
                                },
                                "sink": {
                                    "name": "Ask",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['quotes']['EUR']['date']"
                                },
                                "sink": {
                                    "name": "Date",
                                    "type": "DateTime"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['quotes']['GBP']['ask']"
                                },
                                "sink": {
                                    "name": "Ask",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['quotes']['GBP']['date']"
                                },
                                "sink": {
                                    "name": "Date",
                                    "type": "DateTime"
                                }
                            }
                        ],
                        "collectionReference": "",
                        "mapComplexValuesToString": true
                    }

Answer 1

You have "Ask" in there twice in the sink.