Search code examples
azureazure-sql-databaseazure-data-factoryazure-data-lake

Cannot copy data from azure data lake gen2 to azure sql db using data factory pipeline

Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidColumnName,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The column Prop_0 is not found in target side,Source=Microsoft.DataTransfer.ClientLibrary.

All the part files in the ADLS GEN2 have 8 columns and the sink table also has 8 columns and there is no such column called Prop_0 in part files.

Inputs are part files saved in ADLS GEN2 - enter image description here

Content of one of the part file - enter image description here

Mapping on ADF -

enter image description here

Output of sql query executed on Azure query editor -

enter image description here

Solution

  • You get this error when your source files don’t have a header (or consider first row as header when you have a header for source files) and you have not enabled the column mapping option. Prop_0, Prop_1 etc., will act as column names when the source file does not have a header (or not enabled).

    In this case, when you disable the column mapping option (cleared or skipped), the copy activity will try to insert columns from source using the name, only when they match your sink (table). In the following image, I have not imported the schema (skipped) and it throws the same error when I run the pipeline.

    enter image description here

    enter image description here

    Since your destination does not have Prop_0 column, it throws the following error:

    enter image description here

    Follow the steps specified below to rectify this error:

    • First identify if your source part files have header. Then edit your source dataset by checking/unchecking the first row as header option. Publish and preview this data in the source tab of your pipeline.

    enter image description here

    • Move to Mapping section and click import schemas (Clear and import again if required). Make changes in the mapping if necessary, according to your requirements.

    enter image description here

    enter image description here

    • Changes have to be made in this mapping because the source columns and the destination columns don't match. From the part file sample you have given, the appropriate mapping would be as shown below:

    enter image description here

    • Now run the pipeline. The pipeline will run successfully and the sql table will reflect the rows inserted.

    enter image description here