CODEWITHSUNDEEP
azureazure-synapseazure-data-lake
MahiC98
MahiC98

Reputation: 35

Azure Synapse refuses to use default headers

still a bit new to this and would appreciate some help.

I have a pipeline in Azure Synapse Analytics where it receives a file from an API. The file has the default headers of "Prop_0", "Prop_1" etc... instead of its first row as header.

Went to the settings for that integration dataset and the first row as header option has been selected but it keeps using the default columns.

Integration Dataset settings

How can I fix this?

Is there any way I can manually map the columns?

Upvotes: 0

Views: 276

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11329

As per your question, your source of the file is from an API and the above dataset is your target dataset.

First check the header and schema of your source file whether it contains the correct headers or the Prop_1, Prop_2,.. like headers from the source itself.

To check the schema of the source file, go to your source dataset and click on schema.

enter image description here

If here your headers are like Prop_1, Prop_2,.. then it means these are headers are from your source itself. So, you can change the headers from your API if you want.

Is there any way I can manually map the columns?

If you want to change the headers during the pipeline run, use the mapping in the copy activity.

NOTE: This method will only work correct only if your source is static(without any dymamic parameters in its URI). It won't give any problem with sink dynamic parameters.

Go to copy activity mapping -> click on import schemas. Now, in the destination, you can change the header names as per your requirement.

enter image description here

Now, debug the pipeline and file will be generated with new headers.

enter image description here

If you are dealing with multiple source pages data and your source schema is same for every page,

  • first give any URI of yours in the dataset. Set the mapping in the copy activity like above from the URI's schema.

  • Now, go to the Pipeline JSON and copy the object of the translator key in this.

    enter image description here

  • Again, go the copy activity mapping and here click on dynamic content and give the above JSON in @json('above copied JSON').

    My sample:

    @json('{
                    "type": "TabularTranslator",
                    "mappings": [
                        {
                            "source": {
                                "name": "Prop_0",
                                "type": "String",
                                "physicalType": "String"
                            },
                            "sink": {
                                "name": "Id",
                                "physicalType": "String"
                            }
                        },
                        {
                            "source": {
                                "name": "Prop_1",
                                "type": "String",
                                "physicalType": "String"
                            },
                            "sink": {
                                "name": "Name",
                                "physicalType": "String"
                            }
                        },
                        {
                            "source": {
                                "name": "Prop_2",
                                "type": "String",
                                "physicalType": "String"
                            },
                            "sink": {
                                "name": "Age",
                                "physicalType": "String"
                            }
                        },
                        {
                            "source": {
                                "name": "Prop_3",
                                "type": "String",
                                "physicalType": "String"
                            },
                            "sink": {
                                "name": "DOB",
                                "physicalType": "String"
                            }
                        }
                    ],
                    "typeConversion": true,
                    "typeConversionSettings": {
                        "allowDataTruncation": true,
                        "treatBooleanAsNumber": false
                    }
                }
            }')

    enter image description here

    This ensures that for every page even though the URI is dynamic, the same schema for target will be applied.

Upvotes: 0

Related Questions