Sink Data only if rows exists in the incoming stream (Data Flow)

Srinivas54 20

In Data flow, I am trying to sink data otherwise skip it. I have tried to use surrogate Keys to the rows and used conditional split transformation to check if the rowNumber is equals to 1 ( I actually expect only one row after there data flow transformations). when there are no rows I cannot apply conitions as the expressions (rowNumber == 1 or rowNumber >0 ) end in below exception

User's image

Pipeline
User's image

I do not want to update the sink(JSON dataset) with an empty array. Is there any alternative to achieve it.

Bhargava-MSFT 30,741 Reputation points Microsoft Employee

2024-02-28T19:26:26.0566667+00:00

Hello Srinivas54 ,

Please correct me if my understanding is not correct. it seems like you want to avoid updating the sink with an empty array when there is no data to sink.

You can acheive this by using a conditional split along with an expression to determine whether send the data to sink or not.

add a conditional split transformation to check if there are any rows to sink.

Ex: you can use a conditional split with an expression like RowCount() > 0.

Route the data to two separate paths based on the condition: one path for sinking the data and another path to handle the case where there are no rows.

For the path where there are rows to sink, configure the sink transformation as usual.

For the path where there are no rows, you can choose to do nothing or handle it based on your requirements.

Please watch this video tutorials on how to use the conditional split transformation.

I hope this answers your question. Please let me know if you have any further questions.
Debarchan Sarkar - MSFT 1,131 Reputation points Microsoft Employee

2024-02-28T23:20:14.1866667+00:00
The issue you're experiencing is possibly due to the fact that the Conditional Split transformation in Data Flows doesn't handle empty datasets well. If there are no rows to process, it cannot evaluate any row-based conditions. To avoid exceptions on empty data, you could consider using the Exists or RowCount transformation before your conditional split to check if there are any rows at all. Here's an example: Add an Exists transformation to check if any rows exist in your dataset. The Exists transformation will return a boolean value - true if one or more records exist, and false otherwise. Based on the output of this Exists transformation, you can then use a Switch (or Conditional Split) transformation to control the flow of your data.

Here's how the pseudo-logic might look like:

If Exists returns true, then proceed with Conditional Split (rowNumber == 1) If Exists returns false, then skip to Sink

Please note these are general guidelines and they may not apply directly to your specific use case. Always make sure to thoroughly test your data flows to ensure they handle edge cases (like empty input datasets) correctly.
Srinivas54 20 Reputation points

2024-02-29T15:51:29.1933333+00:00

I did an aggregate transformation to count rows and then used split condition with if rowCount > 0 proceed to next transfromation, else case with no action

Then i did look up to fetch data from previous tranformation(surrogate key) and did one more split condition to stream rows with rowNumber ==1 to sink transfromation

How ever, even if there are no rows, it still sinks empty array to json dataset, after two split conditions. This must be logical bug with Data Factory.
Srinivas54 20 Reputation points

2024-02-29T15:56:18.3533333+00:00

exists transformation asks for condition from two streams, but it doesn't allow to to check if rows exists.

Share via

Sink Data only if rows exists in the incoming stream (Data Flow)

Your answer