Validating a date column with Derived Column Transformation Editor

1
votes

I've got a column coming from text file which supposed to be a date, but can be anything else.

Sometimes it comes as "NULL" and sometimes as Date.

I'd like to write an expression in Derived Column Transformation Editor to validate:

  • when it's "NULL" --> NULL in DT_DATE
  • when it's date --> convert to date
  • when it's not NULL or date --> add to en error table

So far I've got:

MyColumn == "NULL" ? (DT_STR,255,1252)NULL(DT_DATE) : MyColumn

I've added a Script Component to capture the Dervied Column Error Output, but it doesn't work properly. When 1 row moves through, it doesn't commit the error to the error table and the rest to the output table (ends with error for the output).

Not sure what do. Any suggestions?

Thanks.

1
sql-server-2008ssis

1 Answers

1
votes

Try this :-

(MyColumn=="NULL")|| (ISNULL(MyColumn)) ? NULL(DT_DATE) : MyColumn

Update 1:-

(MyColumn=="NULL") ||  (MyColumn(Name))  ? NULL(DT_DATE) :(DT_DATE)MyColumn