3

Can someone advice me please?

enter image description here

I have table in SQL Server

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

When I try insert data from flat file with content

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)

Expression value of Derived Column component is:

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

I got error:

[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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Aunakul
  • 51
  • 5

1 Answers1

1

The error cause is not the blank values, it is the date format. Since DD-MMM-YY format cannot be converted to DT_DBDATE data type.

Try converting the month name to month number and cahnge the date format. As example, the following expression convert DD-MMM-YY format to YYYY-MM-DD format:

([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)  )

References

Hadi
  • 36,233
  • 13
  • 65
  • 124