SSIS expression builder syntax

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  February 21, 2007 at 5:34 pm

  #116287

  Hi,

  I am trying to create a derived column that states the following:

  if PSPUUN <> LB AND MUDMCF =1 THEN PSCNQT/MUCOFA

  if PSPUUN <> LB AND MUDMCF =2 THEN PSCNQT*MUCOFA

  ELSE PSPUUN

  So I've written the following expression in SSIS:

  [PSPEUN] != "LB"  &&  [MUDMCF] == 1 ? [PSCNQT]  /  [MUCOFA] : ([PSPEUN] != "LB"  &&  [MUDMCF] == 2 ?  [PSCNQT] *  [MUCOFA] : [PSPEUN])

  And I'm getting an error regarding regarding incompatible data types. 

  "The data types "DT_NUMERIC" and "DT_WSTR" are incompatible for binary

  operator "==".  The operand types could not be implicitly cast into

  compatible types for the operation.  To perform this operation, one or both

  operands need to be explicitly cast with a cast operator."

  The fields have the following data types:

  MUDMCF (numeric(1,0))

  MUCOFA (numeric(1,0))

  PSPEUN (nchar(3))

  PSCNQT (numeric(15,6)

  I am familiar with the cast TSQL but I'm not sure how to use it in SSIS with Cast Types.  Anyone familiar with this?

  Thanks!

  Isabelle

  Thanks!
  Bea Isabelle

• DESCOURS

  Grasshopper

  Points: 11

  More actions

  February 22, 2007 at 1:38 am

  #691081

  Hi,

  I may have an answer to your question...

  In fact, I think that expressions in SSIS must return only one type in every cases, and that's the why you get the error.

  Let me explain myself, your expression returns the following type :

  if PSPEUN LB AND MUDMCF =1 THEN PSCNQT/MUCOFA -->Numeric

  if PSPEUN LB AND MUDMCF =2 THEN PSCNQT*MUCOFA -->Numeric

  ELSE PSPEUN -->Unicode String

  There is no implicit conversions in SSIS (as in SQL Server 2005) so when SSIS evaluates your expression, it does not know how it should behave with numeric data types and unicode string...

  To solve this problem, in your example, you should tell SSIS how to cast the numeric results of your expression in an unicode string, by using this syntax :

  (DT_WSTR, «length») expression

  You should replace «length» in the expression by an an appropriate length, where the results can fit in (if you try to cast the number 6000 to a 3-char string, you 'll get a truncate error and the execution of your SSIS will fail). To avoid problems in your case, you may use «length» = 30 for instance and the new expression becomes :

  <[PSPEUN] != "LB" && [MUDMCF] == 1 ? (DT_WSTR, 30) [PSCNQT] / [MUCOFA] : ([PSPEUN] != "LB" && [MUDMCF] == 2 ? (DT_WSTR, 30) [PSCNQT] * [MUCOFA] : [PSPEUN])

  Hope this will solve your problems.

  Bye,

  Cyrille

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  February 22, 2007 at 12:13 pm

  #691232

  Hi Cyrille,

  Thanks for the info.  After I read your post I realized that I was using the wrong column at the end.  It should have been a numeric column so that solved my problem!

  Isabelle

  Thanks!
  Bea Isabelle