locked
ADF Copy Activity JSON to SQL mapping error RRS feed

  • Question

  • Hi,

    I was trying to load data into azure data warehouse using Data Factory Copy activity, but continuously get the following error

    "message": "Operation on target Copy data1 failed: Failure happened on 'Sink' side. ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JObject with value {\"@nil\":\"true\"} is not supported yet. Please either remove the targeted column 'DOM_CT_ID' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common,'",

    My data has the following format

    [
      {
        "ANS7_LISTA": {
          "ANS7_ITEM": [
            {
              "data_sped": "2019-12-17T14:52:43.000+01:00",
              "AA_CONSEG_TIT": 2013,
              "AA_ID": 2013,
              "AA_ORD_ID": 2001,
              "AA_REG_ID": 2006,
              "ANS_RIGHE_ID": 132,
              "ANS_TST_ID": 13388,
              "CDS_ID": 87,
              "COD_FIS_EVENTO": "xxxxxxxxxxxx",
              "COERENZA_COD": "L",
              "DATA_EVENTO": 29092014,
              "DOM_CT_ID": {
                "@nil": "true"
              },
              "ID_CORSO_MIUR": 75304,
              "ISCR_ID": 8,
              "MOT_USCITA": "R",
              "PDS_ID": 9999,
              "SEDE_ID": 1059,
              "STATO_SPED_COD": "L",
              "STATO_SPED_OLD": {
                "@nil": "true"
              },
              "STU_ID": 9999999,
              "TIPO_EVENTO_COD": "CC",
              "TIPO_INVIO_COD": "R",
              "TIPO_SPED_ANS_ID": 7,
              "TIPO_TESI": {
                "@nil": "true"
              },
              "VALIDA_FLG": 1,
              "VOTO": {
                "@nil": "true"
              },

              {
              "data_sped": "2019-12-17T14:52:43.000+01:00",
              "AA_CONSEG_TIT": 2013,
              "AA_ID": 2013,
              "AA_ORD_ID": 2001,
              "AA_REG_ID": 2006,
              "ANS_RIGHE_ID": 130,
              "ANS_TST_ID": 13388,
              "CDS_ID": 12,
              "COD_FIS_EVENTO": "xxxxxxxxxxxxxxxxx",
              "COERENZA_COD": "L",
              "DATA_EVENTO": 22012015,
              "DOM_CT_ID": 220800,
              "ID_CORSO_MIUR": 75399,
              "ISCR_ID": 8,
              "MOT_USCITA": "L",
              "PDS_ID": 9999,
              "SEDE_ID": 1059,
              "STATO_SPED_COD": "L",
              "STATO_SPED_OLD": {
                "@nil": "true"
              },
              "STU_ID": 44444444,
              "TIPO_EVENTO_COD": "CC",
              "TIPO_INVIO_COD": "R",
              "TIPO_SPED_ANS_ID": 7,
              "TIPO_TESI": "S",
              "VALIDA_FLG": 1,
              "VOTO": "L107"

          ]
        }
      }
    ]

    As far as I understand the problem is in the fields that accept both normal values and blocks of {"@nil": "true"}.

    I've already looked through several similar topics and added "mapComplexValuesToString": true string to the mapping, but still get the error.

    https://social.msdn.microsoft.com/Forums/en-US/30b7371e-23f6-4cc5-91db-0483ef92d5e6/copy-data-in-adf-using-rest-api-to-sql-database-if-rest-api-has-data-in-json-format?forum=AzureDataFactory

    One of the underlined fields that is shown in the mapping as STATO_SPED_OLD and is recognized as en object doesn't return errors. I suppose because its value is always null. The other, e.g. DOM_CT_ID is identified like integer but sometimes accepts {"@nil": "true"} values. If I add in the mapping @nil subnode, I loose all the correct values of the field and load only "true" value. If I leave the mapping unchanged it produces an error mentioned above. 

    I was not allowed to past and image, so I'll try to load it outside.

    https://www.dropbox.com/s/jn8675lvsq6fz6t/2020-01-18_23-45-17.jpg?dl=0

    How can I adjust my mapping to avoid errors or skipping rows with errors? The best result would be to treat {"@nil": "true"} values like null, but it's ok to copy the field as is and the rest I will do on the adwh.

    Could anybody help me please?

    Thanks,

    Victoria



    • Edited by maty_by Saturday, January 18, 2020 11:01 PM
    Saturday, January 18, 2020 10:58 PM

All replies

  • Hello Victoria and thank you for your question.  This looks like an interesting case.  If I understand you correctly, it is only STATO_SPED_OLD which is causing you an issue.  This one is sometimes a simple value, and sometimes an object.  The fields which are consistent in type do not give you trouble.

    Could you please turn on the Advanced editor option and provide another screenshot?  While you are doing that, I will begin setting up to reproduce the issue in case my idea doesn't pan out.

    Tuesday, January 21, 2020 11:25 PM
  • I found a couple errors in your format which made it not a valid json.  Below I have corrected it (mostly closing braces) until it passed linting.

    [{
    	"ANS7_LISTA": {
    		"ANS7_ITEM": [{
    				"data_sped": "2019-12-17T14:52:43.000+01:00",
    				"AA_CONSEG_TIT": 2013,
    				"AA_ID": 2013,
    				"AA_ORD_ID": 2001,
    				"AA_REG_ID": 2006,
    				"ANS_RIGHE_ID": 132,
    				"ANS_TST_ID": 13388,
    				"CDS_ID": 87,
    				"COD_FIS_EVENTO": "xxxxxxxxxxxx",
    				"COERENZA_COD": "L",
    				"DATA_EVENTO": 29092014,
    				"DOM_CT_ID": {
    					"@nil": "true"
    				},
    				"ID_CORSO_MIUR": 75304,
    				"ISCR_ID": 8,
    				"MOT_USCITA": "R",
    				"PDS_ID": 9999,
    				"SEDE_ID": 1059,
    				"STATO_SPED_COD": "L",
    				"STATO_SPED_OLD": {
    					"@nil": "true"
    				},
    				"STU_ID": 9999999,
    				"TIPO_EVENTO_COD": "CC",
    				"TIPO_INVIO_COD": "R",
    				"TIPO_SPED_ANS_ID": 7,
    				"TIPO_TESI": {
    					"@nil": "true"
    				},
    				"VALIDA_FLG": 1,
    				"VOTO": {
    					"@nil": "true"
    				}
    			},
    			{
    				"data_sped": "2019-12-17T14:52:43.000+01:00",
    				"AA_CONSEG_TIT": 2013,
    				"AA_ID": 2013,
    				"AA_ORD_ID": 2001,
    				"AA_REG_ID": 2006,
    				"ANS_RIGHE_ID": 130,
    				"ANS_TST_ID": 13388,
    				"CDS_ID": 12,
    				"COD_FIS_EVENTO": "xxxxxxxxxxxxxxxxx",
    				"COERENZA_COD": "L",
    				"DATA_EVENTO": 22012015,
    				"DOM_CT_ID": 220800,
    				"ID_CORSO_MIUR": 75399,
    				"ISCR_ID": 8,
    				"MOT_USCITA": "L",
    				"PDS_ID": 9999,
    				"SEDE_ID": 1059,
    				"STATO_SPED_COD": "L",
    				"STATO_SPED_OLD": {
    					"@nil": "true"
    				},
    				"STU_ID": 44444444,
    				"TIPO_EVENTO_COD": "CC",
    				"TIPO_INVIO_COD": "R",
    				"TIPO_SPED_ANS_ID": 7,
    				"TIPO_TESI": "S",
    				"VALIDA_FLG": 1,
    				"VOTO": "L107"
    			}
    		]
    	}
    }]

    Friday, January 24, 2020 9:59 PM
  • Hey, after that lil JSON format fix, and a sidetrack I have a working configuration (pipeline definition below).  I didn't bother to map ALL the columns, so you will need to add in the rest.

    The sidetrack I mentioned had to do with using this JSON in the Dataset setup as an example file for the schema.  It seemed to try to parse the "@nil" as a pipeline expression.  When I cleared the schema from the source dataset (the copy activity left alone) things started moving again.

    {
        "name": "Victoriajsontosql",
        "properties": {
            "activities": [
                {
                    "name": "Copy1",
                    "type": "Copy",
                    "dependsOn": [],
                    "policy": {
                        "timeout": "7.00:00:00",
                        "retry": 0,
                        "retryIntervalInSeconds": 30,
                        "secureOutput": false,
                        "secureInput": false
                    },
                    "userProperties": [],
                    "typeProperties": {
                        "source": {
                            "type": "JsonSource",
                            "storeSettings": {
                                "type": "AzureBlobStorageReadSettings",
                                "recursive": false,
                                "enablePartitionDiscovery": false
                            }
                        },
                        "sink": {
                            "type": "AzureSqlSink"
                        },
                        "enableStaging": false,
                        "translator": {
                            "type": "TabularTranslator",
                            "mappings": [
                                {
                                    "source": {
                                        "path": "data_sped"
                                    },
                                    "sink": {
                                        "name": "data_sped",
                                        "type": "String"
                                    }
                                },
                                {
                                    "source": {
                                        "path": "DOM_CT_ID"
                                    },
                                    "sink": {
                                        "name": "DOM_CT_ID",
                                        "type": "String"
                                    }
                                },
                                {
                                    "source": {
                                        "path": "STATO_SPED_OLD"
                                    },
                                    "sink": {
                                        "name": "STATO_SPD_OLD",
                                        "type": "String"
                                    }
                                },
                                {
                                    "source": {
                                        "path": "TIPO_TESI"
                                    },
                                    "sink": {
                                        "name": "TIPO_TESI",
                                        "type": "String"
                                    }
                                }
                            ],
                            "collectionReference": "$['ANS7_LISTA']['ANS7_ITEM']"
                        },
                        "mapComplexValuesToString": true
                    },
                    "inputs": [
                        {
                            "referenceName": "Victoriajson",
                            "type": "DatasetReference"
                        }
                    ],
                    "outputs": [
                        {
                            "referenceName": "VictoriaSQL",
                            "type": "DatasetReference"
                        }
                    ]
                }
            ],
            "annotations": []
        }
    }

    Friday, January 24, 2020 11:16 PM