Validate .CSV output in ADF

Question

I have a DataFlow that searches an information inside each one of my databases and returns into several .CSV files. When the search returns with data the .CSV contains headers and the data that was founded. When it does not, the .CSV contains only the headers. After that, all the .CSV files are moved into a Sharepoint folder through app logic.

My question is: I need to put those .CSV into two folders "with data" and "no data" to make it easier to check which one of them has or has not data in it. I have tried to use "Conditional Slipt" in my DataFlow but it does not work. Does anyone have any suggestion to deal with that?

Answer 1

As per your scenario since you have incoming files that have rows and empty you can input additional stream with a only header (similar to your CSV with only header and no rows). You can use this to compare with the earlier input stream to decide if it is empty. You could use lookup activity and then use an if activity to decide whether you need to run the copy activity.

In the lookup activity, you could set firstRowOnly as true since you only want to check whether there are data. Check weather the first row is empty, if yes copy that file to folder "no data" else copy that file to folder "with data". Use conditional split here to direct them to different streams to sink (copy) or can use copy activity in the pipeline separately.

From my repro:

1. Consider inputs with and without data (CSV files)

2. Use lookup activity to compare input with a predefined empty source file.

3. Use conditional split activity with suitable condition expression depending on your data schema.

4. Route to appropriate folders using sink

Validate the output:

Refer: Lookup transformation in mapping data flow, Conditional split transformation in mapping data flow, Data transformation expressions in mapping data flow