Azure / Azure/Azure-DataFactory

Copy data activity with stored procedure sink ignores column names

Open
#265 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.