ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression 'codingScheme'

Question

My pipeline (copy activity) fails with the following error: ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression 'codingScheme'

I believe my error arises from the data I am trying to process. The data comes in xml format and is transformed to json by data factory. As it does so, it puts an @ symbol in front of 'codingScheme' in the document.

Then as I try to save the document into my data lake, it reads it as though it's an expression needing to be parsed. I do not need it parsed. In fact I discard that whole bit of the document as I only need the positions and prices, which I extract from the document in my dataflow operation. What would be a sufficient way to work around this issue?

I am unable to affect the format in which I receive the public data from ENTSOE.

//The json document I get from my source:

{"Publication_MarketDocument":{"mRID":"4b10b6d1567d42ebada97250c71fe557","revisionNumber":1,"type":"A44","sender_MarketParticipant.mRID":{"@codingScheme":"A01","_value_":"10X1001A1001A450"},"sender_MarketParticipant.marketRole.type":"A32","receiver_MarketParticipant.mRID":{"@codingScheme":"A01","_value_":"10X1001A1001A450"},"receiver_MarketParticipant.marketRole.type":"A33","createdDateTime":"2023-02-06T13:15:13Z","period.timeInterval":{"start":"2023-02-06T23:00Z","end":"2023-02-07T23:00Z"},"TimeSeries":{"mRID":1,"businessType":"A62","in_Domain.mRID":{"@codingScheme":"A01","_value_":"10YFI-1--------U"},"out_Domain.mRID":{"@codingScheme":"A01","_value_":"10YFI-1--------U"},"currency_Unit.name":"EUR","price_Measure_Unit.name":"MWH","curveType":"A01","Period":{"timeInterval":{"start":"2023-02-06T23:00Z","end":"2023-02-07T23:00Z"},"resolution":"PT60M","Point":[{"position":1,"price.amount":39.94},{"position":2,"price.amount":34.66},{"position":3,"price.amount":33.34},{"position":4,"price.amount":32.47},{"position":5,"price.amount":33.17},{"position":6,"price.amount":36.6},{"position":7,"price.amount":59.7},{"position":8,"price.amount":68.68},{"position":9,"price.amount":63.58},{"position":10,"price.amount":70.93},{"position":11,"price.amount":75.86},{"position":12,"price.amount":69.09},{"position":13,"price.amount":59.98},{"position":14,"price.amount":59.91},{"position":15,"price.amount":56.92},{"position":16,"price.amount":59.9},{"position":17,"price.amount":59.24},{"position":18,"price.amount":74.58},{"position":19,"price.amount":87.65},{"position":20,"price.amount":109.52},{"position":21,"price.amount":113.12},{"position":22,"price.amount":120.55},{"position":23,"price.amount":120.56},{"position":24,"price.amount":117.03}]}}}}

//The source json

{
    "name": "entsoeXmlDayAdheadFi",
    "properties": {
        "linkedServiceName": {
            "referenceName": "entsoeHttpGet",
            "type": "LinkedServiceReference"
        },
        "annotations": [],
        "type": "Xml",
        "typeProperties": {
            "location": {
                "type": "HttpServerLocation",
                "relativeUrl": {
                    "value": "@concat('api?securityToken=', string(pipeline().parameters.entsoeToken), '&documentType=', string(pipeline().parameters.entsoeDocType), '&in_Domain=', string(pipeline().parameters.entsoeAreaCode), '&out_Domain=', string(pipeline().parameters.entsoeAreaCode), '&periodStart=', string(formatDateTime(utcNow(),'yyyyMMdd')), '2300', '&periodEnd=', string(formatDateTime(addDays(utcNow(),1),'yyyyMMdd')),'2300')",
                    "type": "Expression"
                }
            }
        }
    },
    "type": "Microsoft.DataFactory/factories/datasets"
}

//The pipeline json



{

    "name": "pipeline1",

    "properties": {

        "activities": [

            {

                "name": "Copy data1",

                "type": "Copy",

                "dependsOn": [],

                "policy": {

                    "timeout": "0.12:00:00",

                    "retry": 0,

                    "retryIntervalInSeconds": 30,

                    "secureOutput": false,

                    "secureInput": false

                },

                "userProperties": [],

                "typeProperties": {

                    "source": {

                        "type": "XmlSource",

                        "storeSettings": {

                            "type": "HttpReadSettings",

                            "requestMethod": "GET"

                        },

                        "formatSettings": {

                            "type": "XmlReadSettings",

                            "validationMode": "none",

                            "namespaces": true

                        }

                    },

                    "sink": {

                        "type": "JsonSink",

                        "storeSettings": {

                            "type": "AzureBlobFSWriteSettings"

                        },

                        "formatSettings": {

                            "type": "JsonWriteSettings"

                        }

                    },

                    "enableStaging": false

                },

                "inputs": [

                    {

                        "referenceName": "entsoeXmlDayAdheadFi",

                        "type": "DatasetReference"

                    }

                ],

                "outputs": [

                    {

                        "referenceName": "senDataLakeJson",

                        "type": "DatasetReference"

                    }

                ]

            }

        ],

        "parameters": {

            "entsoeToken": {

                "type": "string",

                "defaultValue": "secret-value"

            },

            "entsoeDocType": {

                "type": "string",

                "defaultValue": "A44"

            },

            "entsoeAreaCode": {

                "type": "string",

                "defaultValue": "10YFI-1--------U"

            }

        },

        "annotations": [],

        "lastPublishTime": "2023-02-07T12:25:53Z"

    },

    "type": "Microsoft.DataFactory/factories/pipelines"

}

//The copy activity

{
    "name": "Copy data1",
    "type": "Copy",
    "dependsOn": [],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "source": {
            "type": "XmlSource",
            "storeSettings": {
                "type": "HttpReadSettings",
                "requestMethod": "GET"
            },
            "formatSettings": {
                "type": "XmlReadSettings",
                "validationMode": "none",
                "namespaces": true
            }
        },
        "sink": {
            "type": "JsonSink",
            "storeSettings": {
                "type": "AzureBlobFSWriteSettings"
            },
            "formatSettings": {
                "type": "JsonWriteSettings"
            }
        },
        "enableStaging": false
    },
    "inputs": [
        {
            "referenceName": "entsoeXmlDayAdheadFi",
            "type": "DatasetReference"
        }
    ],
    "outputs": [
        {
            "referenceName": "senDataLakeJson",
            "type": "DatasetReference"
        }
    ]
}

//Actual get request response

<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0">
<mRID>f36b54c1ebc64f57bcb2a7aa405a4d9d</mRID>
<revisionNumber>1</revisionNumber>
<type>A44</type>
<sender_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</sender_MarketParticipant.mRID>
<sender_MarketParticipant.marketRole.type>A32</sender_MarketParticipant.marketRole.type>
<receiver_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</receiver_MarketParticipant.mRID>
<receiver_MarketParticipant.marketRole.type>A33</receiver_MarketParticipant.marketRole.type>
<createdDateTime>2023-02-07T19:52:19Z</createdDateTime>
<period.timeInterval>
<start>2023-02-06T23:00Z</start>
<end>2023-02-07T23:00Z</end>
</period.timeInterval>
<TimeSeries>
<mRID>1</mRID>
<businessType>A62</businessType>
<in_Domain.mRID codingScheme="A01">10YFI-1--------U</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YFI-1--------U</out_Domain.mRID>
<currency_Unit.name>EUR</currency_Unit.name>
<price_Measure_Unit.name>MWH</price_Measure_Unit.name>
<curveType>A01</curveType>
<Period>
<timeInterval>
<start>2023-02-06T23:00Z</start>
<end>2023-02-07T23:00Z</end>
</timeInterval>
<resolution>PT60M</resolution>
<Point>
<position>1</position>
<price.amount>39.94</price.amount>
</Point>
<Point>
<position>2</position>
<price.amount>34.66</price.amount>
</Point>
<Point>
<position>3</position>
<price.amount>33.34</price.amount>
</Point>
<Point>
<position>4</position>
<price.amount>32.47</price.amount>
</Point>
<Point>
<position>5</position>
<price.amount>33.17</price.amount>
</Point>
<Point>
<position>6</position>
<price.amount>36.60</price.amount>
</Point>
<Point>
<position>7</position>
<price.amount>59.70</price.amount>
</Point>
<Point>
<position>8</position>
<price.amount>68.68</price.amount>
</Point>
<Point>
<position>9</position>
<price.amount>63.58</price.amount>
</Point>
<Point>
<position>10</position>
<price.amount>70.93</price.amount>
</Point>
<Point>
<position>11</position>
<price.amount>75.86</price.amount>
</Point>
<Point>
<position>12</position>
<price.amount>69.09</price.amount>
</Point>
<Point>
<position>13</position>
<price.amount>59.98</price.amount>
</Point>
<Point>
<position>14</position>
<price.amount>59.91</price.amount>
</Point>
<Point>
<position>15</position>
<price.amount>56.92</price.amount>
</Point>
<Point>
<position>16</position>
<price.amount>59.90</price.amount>
</Point>
<Point>
<position>17</position>
<price.amount>59.24</price.amount>
</Point>
<Point>
<position>18</position>
<price.amount>74.58</price.amount>
</Point>
<Point>
<position>19</position>
<price.amount>87.65</price.amount>
</Point>
<Point>
<position>20</position>
<price.amount>109.52</price.amount>
</Point>
<Point>
<position>21</position>
<price.amount>113.12</price.amount>
</Point>
<Point>
<position>22</position>
<price.amount>120.55</price.amount>
</Point>
<Point>
<position>23</position>
<price.amount>120.56</price.amount>
</Point>
<Point>
<position>24</position>
<price.amount>117.03</price.amount>
</Point>
</Period>
</TimeSeries>

Answer 1

@Niko Niinimäki Hello and welcome to Microsoft Q&A.

As I understand you are doing copy data from HTTP(XML) to Data Lake Gen 2 (JSON). You have not set schema in sink dataset or set up mapping in copy activity. A property (which you do not want) starts with @ and appears to be causing errors by somehow turning the property name into an expression.

I agree with your assesment so far. The surprising bit is there is no mapping. I expected the problem to be in mapping where @ should be escaped as @@ . I think if you do the Copy Activity mapping you should be able to work around, as well as drop the undesired properties.