Post reply

SSIS String to Date conversion

  • December 26, 2011 at 11:16 pm

    #248723

    Hi All,

    I have a problem in SSIS String to Date conversion

    I read my input data from a text file and is stored in the following format(20111221142944877) and my destination is a datetime datatype column

    I created a derived column and used the below expression

    (DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4) + " " + SUBSTRING([Column 5],9,2) + ":" + SUBSTRING([Column 5],11,2) + ":" + SUBSTRING([Column 5],13,2) + ":" + SUBSTRING([Column 5],15,3))

    Getting this error

    [Derived Column [116]] Error: An error occurred while attempting to perform a type cast.

    [Derived Column [116]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (116)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Clip_Time" (155)" 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.

    Can anyone please help.

    Thanks

  • December 27, 2011 at 1:47 am

    #1425603

    Hi,

    have a look at your string, between seconds and milliseconds should be a "." not a ":"

    Lars

  • December 27, 2011 at 4:11 am

    #1425631

    Thanks for you help.

    I have one more problem

    When i use (DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4) + " " + SUBSTRING([Column 5],9,2) + ":" + SUBSTRING([Column 5],11,2) + ":" + SUBSTRING([Column 5],13,2)) i get the following value

    2011-12-21 14:29:44.000

    Getting all the values as NULL when i use the whole expression((DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4) + " " + SUBSTRING([Column 5],9,2) + ":" + SUBSTRING([Column 5],11,2) + ":" + SUBSTRING([Column 5],13,2) + "." + SUBSTRING([Column 5],15,3))

    Can u please let me know whether i am missing something here

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply