Copy Data Nulling out Destination Delta Tables On Source Connection Error

[IntegrateGuru]

In a data pipeline, if you use a copy data activity and use the 'overwrite' table action, when that activity runs, if it fails to connect to the source, it will overwrite the destination Lakehouse Delta table with null data.

I understand you can add retries - but what if my source is truly unavailable?

Does anyone know a way to prevent copy data from writting to the destination if a connection error (or really any error) occurs on the source?

[Anonymous · Accepted solution]

Hi @IntegrateGuru 

Before running the copy data activity, add a pre-validation step to check the availability of the source. If the source is unavailable, you can stop the pipeline execution.

For example, the process could be:

1. Add a Get Metadata activity to connect to the data source and check some meta data from it. You can also try Lookup activity or other activities according to the data source. For example, if a table is to be copied, get the number of rows of the source table. 
2. Add an If Condition activity to check the output of previous activity. If the output is desired indicating the data source is ready, returns True. Otherwise returns False. For exmaple, check whether the number of table rows is greater than 0. 
3. In previous step's If Condition activity, execute the Copy data activity in True scenario. 

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[IntegrateGuru]

This certainly is a solution - not ideal though. I don't understand why the default behavior when you can't connect to the source is to truncate your destination. But this is a workaround to that issue I suppose. However - if there are intermittent connectivity issues, then this would not fix those.

Is there anywhere I can suggest a change to this behaviour?

[Anonymous]

Hi @IntegrateGuru 

I agree. Maybe it would be better to check the data source connection in advance. You can raise ideas and suggestions at Fabric Ideas Forum. Product team will review the ideas. 

Best Regards,
Jing

[FabianSchut]

I did not check this, but this may be resolved if you enable staging in the copy activity.