0

Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have (DT_DATE)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2)), but it's not working.

I GET THESE ERRORS:

  1. [Derived Column [2]] Error: An error occurred while attempting to perform a type cast.
  2. [Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Inputs[Derived Column Input].Columns[Drv DOB]" 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.

•470324 •470324 •470209 •101 •0

apariyar
  • 35
  • 2
  • 11

2 Answers2

0

YEAR have 4 digits not 2, so it would be:

(DT_DATE)("20" + SUBSTRING([Drv DOB],1,2) + "-" + 
                 SUBSTRING([Drv DOB],3,2) + "-" + 
                 SUBSTRING([Drv DOB],5,2))
Justin
  • 9,634
  • 6
  • 35
  • 47
  • OK WITH THIS : (DT_DATE)(SUBSTRING("20" + [Drv DOB],1,2) + SUBSTRING([Drv DOB],3,2) + SUBSTRING([Drv DOB],5,2)) [Derived Column [2]] ........I GET THIS ERROR Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Inputs[Derived Column Input].Columns[Drv DOB]" 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. – apariyar Aug 20 '14 at 14:55
  • This works..................................................... ([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2)) – apariyar Aug 21 '14 at 15:47
0

This works for me! Thanks everyone 

([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2))
apariyar
  • 35
  • 2
  • 11