none
How to solve Type cast error in Derived column

    Question

  • I have Delimited text file where I'm loading into Database table

    In Text file

    1. COL1 : Predicted delivery date : DT_STR Format

    2.COL2  : ScanDateTime : DT_STR Format

    In Destination Table :

    1. COL1 : Predicted delivery date : DATE  (DataType)

    2.COL2  : ScanDateTime : DATETIME and I need to load data in this below format

    Switchoffset (Substring(ScanDateTime , 1, 22)+':'+Substring(ScanDateTime , 23, 24),'-05:00')

    I have trying to load this data into destination table using Derived column :

    I gave this expression for  Col 1  : (DT_DBDATE)LEFT([Predicted Delivery Date],10)

    col2  : (DT_DATE)(SUBSTRING([ScanDateTime ],1,4) + "-" + SUBSTRING([ScanDateTime ],5,2) + "-" + SUBSTRING([ScanDateTime ],7,2),'-05:00')

    But both are giving error :

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


    [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.Outputs[Derived Column Output].Columns[Predicted]" 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.

    For Col1 : Predicted delivery date how I need to handle Null values and Load the data into Destination table which is Date Format

    For Col2  : ScanDateTime : How I can write an expression for Switchoffset (exp) - 5hrs for that date time column and load  


    • Edited by mohan1111 Sunday, May 19, 2019 8:45 AM modified
    Sunday, May 19, 2019 8:41 AM

All replies

  • Hi mohan1111,

    According to your issue, we could use the following expression in derived column.

    Expression:

    COL1: [Predicted delivery date] == "" ? NULL(DT_DBDATE) : (DT_DBDATE)[Predicted delivery date]

    COL2: DATEADD("Hh",-5,(DT_DATE)[Scan Date Time])

    Plese refer to DATEADD(SSIS Expression) .

    I hope my solution would be helpful.

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 20, 2019 6:47 AM
  • I have tried this way also but it got failed
    Monday, May 20, 2019 10:22 AM
  • Hi mohan1111,

    It is very possible that the problem is with the actual data in the source file.

    Please share few lines from the file verbatim.

    Monday, May 20, 2019 1:38 PM
  • Hi mohan1111,

    Could you please share the error messages that you get now?

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 21, 2019 7:45 AM