I have multiple json files, I am currently using for each to load them to database. But this is very time consuming. Is there any other way to load them to database?

Question

I have multiple json files in the below format. I have atleast 2000 files.

json1: {
"col1": false,
"col2":true,
"col3": value1
}

json2: {
"col1": false,
"col2":true,
"col3": value1
}

json3: {
"col1": false,
"col2":true,
"col3": value1
}

I need to read them from the Azure datalake to Azure datafactory using dataflow(I need to do some transformations) and load them to Synapse database. I am currently using foreach loop which is very time consuming.

I tried to load using wildcard characters. It just loaded the first file and didn't load the others.

Is there any other way to load them quickly to Synapse.?

Answer 1

You can use the file path and wildcard in the data flow source to load all of the JSON files into a single stream using a single data flow activity instead of iterating over a list with For Each.

Answer 2

Wildcard (*) works with Json as well.

I initially had sampling enabled in the dataflow which is why it looked at only one file.

Once I disabled, the sampling, it worked fine.

Thanks for the answers.

Answer 3

Thanks for the answer, this helps.

I have a connected question around this : For example if I have 10 different JSON files in input source folder and I need to flatten, again name them with same original file name in the output folder, how can I do that ? Please suggest @MartinJaffer-MSFT

Answer 4

Hi @MartinJaffer-MSFT

I think you missed one detail in my question - I specifically want the output file names to be exactly same as the original file names. Is there a way or option to do that? This is critical as in my database I have logics built around the file names as well.