sql - SSIS Derived Column Expression -

i have below derived expression timestamp in ssis

(dt_dbtime)(substring([column 11],1,2) + ":" + substring([column 11],3,2) + ":" + substring([column 11],5,2))  

but it's not taking null values tried 1

isnull ( [column 11] ) ? "000000" : (dt_dbtime)(substring([column 11],1,2) + ":" + substring([column 11],3,2) + ":" + substring([column 11],5,2))  

but still not working.

can on this?

the error posted in question bit misleading.

the actual error produced in ssdt :

the data types "dt_wstr" , "dt_dbtime" incompatible conditional operator. operand types cannot implicitly cast compatible types conditional operation. perform operation, 1 or both operands need explicitly cast cast operator.

and ofcourse 1 posted @ end:

error @ data flow task [derived column [18]]: computing expression "(isnull([column 0]) ? "000000" : (dt_dbtime) (substring([column 0],1,2) + ":" + substring([column 0],3,2) + ":" + substring([column 0],5,2)))" failed error code 0xc00470a0. expression may have errors, such divide zero, cannot detected @ parse time, or there may out-of-memory error.

in case means cannot define (dt_dbtime) 1 part of conditional operation,

else part

: (dt_dbtime)(substring([column 11],1,2) + ":" + substring([column 11],3,2) + ":" + substring([column 11],5,2))

leaving other part "dt_wstr"

if part

isnull ( [column 11] ) ? "000000"

so correct transformation rule should be:

(dt_dbtime)(is( [column 11] ) ? "000000" : (substring([column 11],1,2) + ":" + substring([column 11],3,2) + ":" + substring([column 11],5,2)))