Azure Data Factory Copy activity swaps values from Rest API JSON into SQL data table type (for sink stored procedure)

Question

If the order of columns of my MS SQL Server User-Defined TableType is not exactly like the order of the JSON fields in the result of my rest API call the values are in the worng order. The mapping in the data copy activity looks fine, but the data is inserted in the wrong columns. At least if the datatypes allow it. Otherwise i recieve a database error. It almost looks if the mapping is based on the ordinal position.

Has someone experienced similar behaviour? And if so, wer you able to solve this problem?
I would expect if the mapping is based on name, it should also behave accordingly.

Here is the definition and a screenshot of the mapping:

{
"name": "Copy CertificationOrganisation",
"type": "Copy",
"dependsOn": [
{
"activity": "GetBearerToken",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "RestSource",
"httpRequestTimeout": "00:01:40",
"requestInterval": "00.00:00:00.010",
"requestMethod": "GET",
"additionalHeaders": {
"Authorization": {
"value": "@markus.bohland@hotmail.de ('Bearer ', activity('GetBearerToken').output.access_token)",
"type": "Expression"
}
}
},
"sink": {
"type": "SqlServerSink",
"sqlWriterStoredProcedureName": "[dbo].[axspADFSinkFLC_CertificationOrganisation]",
"sqlWriterTableType": "FLC_CertificationOrganisation",
"storedProcedureTableTypeParameterName": "CertificationOrganisationType"
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "['expiry_date']"
},
"sink": {
"name": "expiry_date",
"type": "DateTime"
}
},
{
"source": {
"path": "['entry_date']"
},
"sink": {
"name": "entry_date",
"type": "DateTime"
}
},
{
"source": {
"path": "['id']"
},
"sink": {
"name": "id",
"type": "Int32"
}
},
{
"source": {
"path": "['name']"
},
"sink": {
"name": "name",
"type": "String"
}
},
{
"source": {
"path": "['short_name']"
},
"sink": {
"name": "short_name",
"type": "String"
}
},
{
"source": {
"path": "['change_date_time']"
},
"sink": {
"name": "change_date_time",
"type": "DateTime"
}
}
],
"collectionReference": "$['value']",
"mapComplexValuesToString": true
}
},
"inputs": [
{
"referenceName": "FloricodeCertificationOrganisationREST",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "SQLCertificationOrganisation",
"type": "DatasetReference"
}
]
}

Answer 1

Hi @Niels Dekkers ,

Welcome to Microsoft Q&A platform and thanks for posting your question.

As I understand your query, you are trying to copy the data from REST API to MySQL database using copy activity. But the order of the columns in MySQL database is different than the source . Please let me know if my understanding is incorrect.

I tried to reproduce the same scenario with some dummy data and it worked for me . Note: As target database, I used Az SQL server (Not MySQL).

My suggestion would be to drop the target table and use Auto create option in the sink settings of copy activity to generate the target table schema . Once that is done after the first run, you can consider importing the schema in mapping tab and map each columns correctly.

Kindly let us know if it helps.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators