sql - 为什么派生列组件的表达式不能在 SSIS 中使用空值？

Question

有人可以给我建议吗？

我在 SQL Server 中有表

create table t_test_date 
(
  tcol_date date, 
  tcol_varchar varchar(20), 
  tcol_date1 date
)

当我尝试从带有内容的平面文件插入数据时

COL_DATE
20-DEC-33
20-NOV-33
10-MAY-28
10-NOV-21

10-MAY-17
11-MAY-17
--(To Allow has empty values)

Derived Column组件的表达式值为:

COL_DATE == "" || ISNULL(COL_DATE) ? NULL(DT_DBDATE) : (DT_DBDATE)COL_DATE

我得到错误:

[Derived Column [17]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[COL_DATE]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Answer 1

错误原因不是空白值，而是日期格式。由于DD-MMM-YY格式无法转换为DT_DBDATE数据类型。

尝试将月份名称转换为月份编号并更改日期格式。例如，以下表达式将 DD-MMM-YY 格式转换为 YYYY-MM-DD 格式:

([COL_DATE] == "" || ISNULL([COL_DATE])) ? NULL(DT_DATE) :
(DT_DATE)("20" + RIGHT([COL_DATE],2) + "-" + 
(SUBSTRING( [COL_DATE],4,3) == "JAN" ? "01" :
SUBSTRING( [COL_DATE],4,3) == "FEB" ? "02" : 
SUBSTRING( [COL_DATE],4,3) == "MAR" ? "03" :
SUBSTRING( [COL_DATE],4,3) == "APR" ? "04" :
SUBSTRING( [COL_DATE],4,3) == "MAY" ? "05" : 
SUBSTRING( [COL_DATE],4,3) == "JUN" ? "06" :
SUBSTRING( [COL_DATE],4,3) == "JUL" ? "07" :
SUBSTRING( [COL_DATE],4,3) == "AUG" ? "08" :
SUBSTRING( [COL_DATE],4,3) == "SEP" ? "09" : 
SUBSTRING( [COL_DATE],4,3) == "OCT" ? "10" : 
SUBSTRING( [COL_DATE],4,3) == "NOV" ? "11" : 
SUBSTRING( [COL_DATE],4,3) == "DEC"? "12": "00") 
+ "-" + LEFT([COL_DATE],2)  )

引用资料

• Convert month name to month number in SSIS