CODEWITHSUNDEEP
arraysjsonpipelineazure-data-factory
Hennie
Hennie

Reputation: 1

How to transform JSON file data directly in a Azure Data Factory pipeline

I want to copy a JSON file into Azure SQL table, but it has nested Hierarchy, I only need the fields/values of the fields Ergon_Businessunit_code, KPL, Ergon_Functiegroep_code, Aantal. How to do that? I tried with lookup en foreach, but got array errors

enter image description here

Upvotes: 0

Views: 2599

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

Yes as Mark Kromer mentioned, you can copy JSON data to the Azure SQL table using the dataflow activity.

Here are the steps to copy JSON to Azure SQL using dataflow:

  1. Create a JSON dataset and connect it to the source.

enter image description here

  1. Source data preview:

enter image description here

  1. Connect the source output to flatten transformation and under Unroll by and Unroll root, select "rows" array.

  2. You can remove the columns which not required to pass to the sink.

enter image description here

  1. Data preview after it is flattened.

enter image description here

  1. Add sink transformation and connect to Azure SQL table dataset.

enter image description here

  1. In mapping, add or remove mapping as per the output columns.

enter image description here

  1. Output data of sink.

enter image description here

  1. Add the dataflow activity to the pipeline and execute it.

enter image description here

  1. Data loaded to Azure SQL table.

enter image description here

Upvotes: 2

Related Questions