You have "Ask" in there twice in the sink.
copy from json as source to azure database
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
}