ADF Copy Data Activity converts NULL data value to string '' from Azure Table Storage to CRM

Question

Hi,

I am using the Copy data activity in ADF pipeline from Azure Table Storage as source to Dataverse CRM as sink.

Some date fields in the source data have values, while others don't. Everything works well when the date fields have values, but I encounter an error when they don't.

"ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'datefieldname' from type 'String' (precision:, scale:) to type 'DateTime' (precision:, scale:). Additional info: String was not recognized as a valid DateTime."

On the "Sink" tab of the copy data activity, there's an option called 'Ignore null values.' I've tried both checking and unchecking it, but I still encounter the same error.

I found a similar problem reported on the Microsoft community, and it appears that it hasn't been resolved yet.

https://learn.microsoft.com/en-us/answers/questions/1398225/in-adf-copy-activity-null-values-in-source-are-con

Additionally, I'm unable to utilize 'Data flows' in Azure Data Factory because Azure Table Storage isn't supported as a source, whether as a dataset or inline.

I'm not certain if this issue has been resolved, but any assistance would be greatly appreciated.

Thank you!

Answer 1

To work around this issue, you can add a Derived Column transformation in the Copy Data activity to explicitly handle these NULL values before the data is written to the sink. Unfortunately, as you've mentioned, ADF Data Flows do not directly support Azure Table Storage as a source. However, you can use a combination of ADF activities to achieve the desired transformation.

Develop an Azure Function or Azure Logic App to read data from Azure Table Storage and replace NULL date fields with a specific placeholder (like '1970-01-01' or another invalid date that you can later identify and replace with NULL in the sink).

Then , add an HTTP activity in your ADF pipeline to call the Azure Function/Logic App, which processes the data and returns it in a suitable format.

Use the output from the Azure Function/Logic App as the source in your Copy Data activity.