Azure数据工厂数据流以静默方式使日期列为NULL [英] Azure data factory data flow silently NULLing date column

问题描述

我正在尝试使用Azure数据工厂将CSV插入到Azure SQL表中.在我检查结果之前，一切似乎都还不错.列之一是可为空的日期.CSV包含类似 1/2/2020 12:00:00 AM 的值.数据流以静默方式插入 NULL 而不是抛出错误，因为它不喜欢输入.那么，如何获取数据流以将字符串正确地转换为日期时间，然后在以后出现类似问题时出错呢?我真的不希望出现静默故障和不良数据.

解决方案

空值是由于ADF中的日期格式不兼容.您需要进行日期格式转换.

您的原始日期格式是否为 MM/dd/yyyy HH:mm:ss ?
如果是这样，您可以使用

然后我添加表达式 toString(toTimestamp(EmpDate，'MM/dd/yyyy HH:mm:ss')，'yyyy-MM-dd HH:mm:SS')在派生"列活动中.在这里，您可以选择所需的日期格式.

3.根据Mark Kromer的建议，我直接在派生列"之后添加条件拆分，并检查isNull(EmpDate).在这里，我使用 not(isNull(EmpDate))表达式.

4. 最后，如果 EmpDate 包含空值，它将进入接收器2，否则进入接收器1.该行包含空值:

I'm trying to use Azure Data Factory to upsert a CSV into an Azure SQL table. All seemed well until I checked the results. One of the columns is a nullable date. The CSV contains a value like so 1/2/2020 12:00:00 AM. The data flow silently inserts a NULL instead of throwing an error because it didn't like the input. So how can I get my data flow to convert the string to a datetime properly, and then to error out on issues like this in the future? I really don't want silent failures and bad data.

解决方案

The null value is due to incompatible date formats in ADF. You need to do date format conversion.

Is your source date format like this MM/dd/yyyy HH:mm:ss?
If so, you can use Derived column and add the expression toString(toTimestamp(<Your_Column_Name>,'MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd HH:mm:SS') to format this column to String. It solved the NULL value. Of course you can choose what the date format you want.

I made a test as follows:

1. My data source is from a csv file and the EmpDate is a date type like yours and last row contains a null value.

2. Then I add the expression toString(toTimestamp(EmpDate,'MM/dd/yyyy HH:mm:ss'),'yyyy-MM-dd HH:mm:SS') in the Derived column activity. Here you can choose the date format what you want.

3.According to Mark Kromer's suggestion, I Add Conditional Split directly after the Derived Column and check for isNull(EmpDate). Here I use not(isNull(EmpDate)) expression.

4. In the end, if the EmpDate contains null value, it will go to sink2 else go to sink1. The row contains null value:

这篇关于Azure数据工厂数据流以静默方式使日期列为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！