SSIS convert string to datetime/date

Question

Trying to import data into SQL table from Excel. One of the columns "RptDt" on Excel file is datetime but sometime it has some string/varchar values "Null" (not Null). OleDestination has column "RptDt" with data type "Dt_date".

I get an error because SSIS takes this "Null" as string when trying to transfer the data to sql.

I'm trying to use derived column but i'm not sure if i have right expression to satisfy dates and string value.

 ISNULL([RptDt] ? (DT_WSTR, 50)("Null") :NULL(DT_DBDATE) : (DT_DBDATE) [RptDt]

Sample column value on RptDt.

08/09/2013

10/09/2013 
Null
NULL

All i'm trying to do is, If column value is string then pass Null value if not pass date.

Justin Justin · Accepted Answer · 2013-09-20T10:11:14

If you can insert NULL in your table use this derived column code:

 ISNULL([RptDt]) || [RptDt]=="Null" ? NULL(DT_DBDATE) : (DT_DBDATE)[RptDt]

SSIS convert string to datetime/date

1 Answers