Incremental copy with watermark throws "Object reference not set to an instance of an object"

Question

Morning,

I'm currently using the watermark method to incrementally copy a few tables from another SQL Server instance: https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview#delta-data-loading-from-database-by-using-a-watermark

We've been doing this for several weeks already without any issues, but it's been two days where we receive the following error message:

]1

I've checked both source and sink, and everything is just fine. The old and new watermark values are the expected, and actually, the process that are failing, are the ones that have the same old and new watermark, which means there's nothing new to copy.

Why am I getting this errors? What do they mean? How can I solve this?

Regards.

Answer 1

Hello and welcome back to Microsoft Q&A @Francisco Dominguez .

The ask as I understand, is an explanation, and if possible, fix for the error message "Object reference not set to an instance of an object".

I can explain the general meaning of this error message, and explain how it could make sense in the context of having nothing new to copy. For more precise explaination, I would need to know which activity is raising this error message.

To make this explanation more relatable, take a moment to imagine you have a job where each day you go into a warehouse and pick up a box and take the box outside. You have been doing this so long you can, and do, this blind. The one day your hands close on the where the box should be, and there is nothing, just empty air. That is what this error message is saying, "I'm trying to pick up something which isn't there! So I can't pick it up." Normally this doesn't sound so bad, but what if you job also required you to apply a packing lable to the box. You got a lable, but nothing to stick it to, and that breaks process.

The obvious solution is to alter the instructions to say "If there is a box, pick it up and take it outside, otherwise do nothing." In the context of Data Factory, this would take the form of an If Conditional / Activity. If the old watermark == the new watermark, then do nothing, otherwise, do the copy activity (or whatever is making the error).

Please let me know if what I have said makes sense so far, and whether I seem to be on the right track.