Ssis find diff between 2 columns

  • How to substract values from column a of table a to Column b of table a( yes its a same table) using ssis??

    Can we use execute sql task and write t-sql code??

    Or which transformation to use??

  • Is this as part of a data flow? If yes, add a derived column to your data flow and put the calculation in there.

    ExecuteSQL tasks do exactly what they say – so if you can do what you need to do in T-SQL, you can do it using an ExecuteSQL task. Based on what you say, this sounds like the better solution: a simple UPDATE query.

    • This reply was modified 5 years, 6 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks. It's inside Data Flow task and I have hard time compiling this IF condition.

    IF TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = 0 then 0 Else 1.

    Here is  what I wrote but error, [TOTAL_BILL_AMT] - [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"

    Also I tried,

    (DT_NUMERIC,10,2) [TOTAL_BILL_AMT] - (DT_NUMERIC,10,2)) [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"

    Error at Data Flow Task [Derived Column [170]]: Attempt to parse the expression "(DT_NUMERIC,10,2) [TOTAL_BILL_AMT] - (DT_NUMERIC,10,2)) [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

    • This reply was modified 5 years, 6 months ago by  dallas13.
  • What are the data types of TOTAL_BILL_AMT and TOTAL_BILL_AMT_PRD within the data flow?

    Also, try removing the quotes throughout the expression – if the 0, 0 and 1 are supposed to be integers, they should not require them.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks. Datatypes for both are decimal(10,2)

    if the difference =0 then insert 1 (bit datatype) or 0

  • Thanks it worked finally. Really appreciate it.

    (DT_NUMERIC,10,2)TOTAL_BILL_AMT - (DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD == 0 ? 0 : 1

  • Please also try this ... you shouldn't need those casts:

    TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD == 0 ? 0 : 1

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • IF TOTAL_BILL_AMT_PROD = NULL THEN NULL ELSE (TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = O then 0 Else 1 )

    ISNULL(TOTAL_BILL_AMT_PRD) == ""? NULL(DT_STR, 4, 1252) : ((DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD - DT_NUMERIC,10,2)TOTAL_BILL_AMT == 0 ? 1 : 0) gives error.

  • dallas13 wrote:

    IF TOTAL_BILL_AMT_PROD = NULL THEN NULL ELSE (TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = O then 0 Else 1 ) ISNULL(TOTAL_BILL_AMT_PRD) == ""? NULL(DT_STR, 4, 1252) : ((DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD - DT_NUMERIC,10,2)TOTAL_BILL_AMT == 0 ? 1 : 0) gives error.

    Is this for our information, or is there a question lurking in there somewhere?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Try the following:

    ISNULL(TOTAL_BILL_AMT_PRD) == True ? (DT_NUMERIC,1,0) NULL : ((TOTAL_BILL_AMT_PRD - TOTAL_BILL_AMT) == 0 ? 1 : 0)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 10 posts - 1 through 9 (of 9 total)

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