CODEWITHSUNDEEP
azureazure-data-factoryazure-data-lakedataverse
Shashidhar Sunagar
Shashidhar Sunagar

Reputation: 13

Not able to see column headers in Datasets of Azure Data factory

I have imported Dataverse tables into AZ Data lake using Azure synapse link in Powerapps. I got the tables in CSV format in AZ data lake container. Now using azure data factory, I Want to drill down this data with some filters and sink it into new csv file. While achieving this, I created dataset for each csv file and both csv file and dataset doesn't show up the column headers. I did select "First row as header" option but still no luck. Please help me if anyone has gone through the same and fixed this issue.

Upvotes: 1

Views: 2246

Answers (2)

Jagdish Wagh
Jagdish Wagh

Reputation: 31

I faced a similar issue while extracting data from a Dataverse table using Data Factory. Columns with null values were not being transferred to the CSV file or SQL Server target table.

Follow below steps for testing the flow and if is it working fine then you can do some automation.

  1. Create one pipeline and add copy activity
  2. Cerate a dataverse dataset(link service created using AD or SPN)
  3. In copy setup source dataverse data set and sink will be csv file.
  4. Create one parameter of the pipeline p_columnmappuing or setup variable and datatype should be string.
  5. Pass that parameter to mapping area in copy activity as below @json(p_columnmapping) Dataverse XML Script(Source Query)
<fetch>  
  <entity name="go_todo">  
    <attribute name="go_appointmentid" /> 
    <attribute name="go_category" /> 
    <attribute name="go_department" /> 
    <attribute name="go_group" /> 
    <attribute name="go_name" /> 
    <attribute name="go_rating" />  
    <attribute name="go_reason_negative_rating" /> 
    <attribute name="go_reason_positive_rating" />
    <attribute name="go_relevance" />  
    <attribute name="go_adaptionstatus" />
  </entity>  
</fetch>



Parameter column mapping:

    {"type":"TabularTranslator","mappings":[{"source":
    {"name":"go_appointmentid","type":"Guid"},"sink":{"name":"go_appointmentid","type":"String"}},{"source":{"name":"go_category","type":"String"},"sink":{"name":"go_category","type":"String"}},{"source":{"name":"go_department","type":"Int32"},"sink":{"name":"go_department","type":"String"}},{"source":{"name":"go_group","type":"String"},"sink":{"name":"go_group","type":"String"}},{"source":{"name":"go_name","type":"String"},"sink":{"name":"go_name","type":"String"}},{"source":{"name":"go_rating","type":"String"},"sink":{"name":"go_rating","type":"String"}},{"source":{"name":"go_reason_negative_rating","type":"Int32"},"sink":{"name":"go_reason_negative_rating","type":"String"}},{"source":{"name":"go_reason_positive_rating","type":"Int32"},"sink":{"name":"go_reason_positive_rating","type":"String"}},{"source":{"name":"go_relevance","type":"String"},"sink":{"name":"go_relevance","type":"String"}},{"source":{"name":"go_adaptionstatus","type":"String"},"sink":{"name":"go_adaptionstatus","type":"String"}}]}

[![column mapping expression][1]][1]


  [1]: https://i.sstatic.net/H3EuDC2O.png

Upvotes: 0

Mutaz-MSFT
Mutaz-MSFT

Reputation: 806

When you ingest dataverse into data lake using the synapse link, the data will stored in a CDM format. In CDM, the schema is not stored in the CSVs as header, it is stored in the model.json file.

The proper way to read CDM in data factory is by using mapping data flows with source set as Common Data Model: https://learn.microsoft.com/en-us/powerapps/maker/data-platform/export-to-data-lake-data-adf

this will take care of schema handling for you, and knows how to read the correct files for entities in the CDM structure.

Upvotes: 2

Related Questions