Azure / Azure/Azure-DataFactory
Copy data activity with stored procedure sink ignores column names
- Dominant language
- PowerShell
- Stars
- 529
- Forks
- 623
- PR merge metrics
- No merged PRs in 30d
Description
I have a copy activity with Azure SQL Sink using stored procedure, as described here: https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server#invoke-a-stored-procedure-from-a-sql-sink
The source is REST API returning JSON.
It seems that names specified for sink columns in mappings are ignored. Instead, values are assigned to TVP columns in the order of mappings. So columns get mixed up. The only workaround is to create a type for parameter that exactly matches the order of columns in the source data.
In the following example, Description is being written to Frequency column, Frequency to Unit, Unit to Description.
Type definition:
` VariableCode varchar(100) NOT NULL,
Frequency varchar(50) NOT NULL,
Unit nvarchar(50) NULL,
Description nvarchar(200) NOT NULL,
Adjustment nvarchar(100) NULL
`
The mappings are the following:
` "mappings": [
{
"source": {
"path": "$['seriess'][0]['id']"
},
"sink": {
"name": "VariableCode",
"type": "String"
}
},
{
"source": {
"path": "$['seriess'][0]['title']"
},
"sink": {
"name": "Description",
"type": "String"
}
},
{
"source": {
"path": "$['seriess'][0]['frequency']"
},
"sink": {
"name": "Frequency",
"type": "String"
}
},
{
"source": {
"path": "$['seriess'][0]['units']"
},
"sink": {
"name": "Unit",
"type": "String"
}
},
{
"source": {
"path": "$['seriess'][0]['seasonal_adjustment']"
},
"sink": {
"name": "Adjustment",
"type": "String"
}
}
],`
Contributor guide
Assessment
This issue has not been assessed yet.