none
Validation error from Derived Task

    Question

  • SSIS validation error:

    Error at Data Flow Task [Derived Column [120]]: The data type "DT_WSTR" cannot be used with binary operator "-". The type of one or both of the operands is not supported for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

    I am trying to load a file which has amount column as text with leading spaces and in some case space and negative sign. Example of amount '      -1023.78'.

    The destination colum is numeric(18,2). Using derived column  task, I’ve tried thses 3 expressions and they all get the same error.

    SUBSTRING(LTRIM(AMOUNT),1,1) == "-" ? (DT_NUMERIC,18,2)SUBSTRING(LTRIM(AMOUNT),2,LEN(LTRIM(AMOUNT) - 1)) * -1 : (DT_NUMERIC,18,2)(LTRIM(AMOUNT))

    FINDSTRING(AMOUNT,"-",1) == 1 ? (DT_NUMERIC,18,2)SUBSTRING(LTRIM(AMOUNT),2,LEN(LTRIM(AMOUNT) - 1)) * -1 : (DT_NUMERIC,18,2)(LTRIM(AMOUNT))

     

    SUBSTRING(REPLACE((LTRIM(AMOUNT)),"-","y"),1,1) == "y" ? (DT_NUMERIC,18,2)SUBSTRING(LTRIM(AMOUNT),2,LEN(LTRIM(AMOUNT) - 1)) * -1 : (DT_NUMERIC,18,2)(LTRIM(AMOUNT))

    What is the issue?

    Wednesday, February 12, 2014 8:42 PM

Answers

  • SUBSTRING(LTRIM(AMOUNT),1,1) == "-" ? 
    (DT_NUMERIC,18,2)SUBSTRING(LTRIM(AMOUNT),2,LEN(LTRIM(AMOUNT)) - 1) * -1 
    : (DT_NUMERIC,18,2)(LTRIM(AMOUNT))
    Your closing parenthesis after -1 should be after the closing parenthesis that closes the LTRIM statement.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by jandho Thursday, February 13, 2014 1:56 PM
    • Marked as answer by Maggie3614 Thursday, February 13, 2014 2:07 PM
    Wednesday, February 12, 2014 9:10 PM
  • Russ,

    I used yours and got pass that error.

    Thanks!

    • Marked as answer by Maggie3614 Thursday, February 13, 2014 2:00 PM
    Wednesday, February 12, 2014 9:33 PM

All replies

  • SUBSTRING(LTRIM(AMOUNT),1,1) == "-" ? 
    (DT_NUMERIC,18,2)SUBSTRING(LTRIM(AMOUNT),2,LEN(LTRIM(AMOUNT)) - 1) * -1 
    : (DT_NUMERIC,18,2)(LTRIM(AMOUNT))
    Your closing parenthesis after -1 should be after the closing parenthesis that closes the LTRIM statement.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by jandho Thursday, February 13, 2014 1:56 PM
    • Marked as answer by Maggie3614 Thursday, February 13, 2014 2:07 PM
    Wednesday, February 12, 2014 9:10 PM
  • Russ,

    The error occurs during package execution, not at time of expression creation.

    Wednesday, February 12, 2014 9:19 PM
  • All I know is that I got the error at expression creation when I used your formula.  I changed to use my expression and I neither got an error in design time nor when I ran it in debug mode.

    Did you use my formula exactly?  What formula did you use?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, February 12, 2014 9:22 PM
  • Russ,

    I used yours and got pass that error.

    Thanks!

    • Marked as answer by Maggie3614 Thursday, February 13, 2014 2:00 PM
    Wednesday, February 12, 2014 9:33 PM
  • My suggestion is that you mark the post that represents the answer.  It clarifies which of the posts actually answered your question so that others don't need to scroll through the thread to find the answer.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, February 13, 2014 2:06 PM