Export (0) Print
Expand All
2 out of 3 rated this helpful - Rate this topic

? : (Conditional) (SSIS)

Returns one of two expressions based on the evaluation of a Boolean expression. If the Boolean expression evaluates to TRUE, then the first expression is evaluated and the result is the expression result. If the Boolean expression evaluates to FALSE then the second expression is evaluated and its result is the expression result.



boolean_expression?expression1:expression2
        
boolean_expression

Is any valid expression that evaluates to TRUE, FALSE, or NULL.

expression1

Is any valid expression.

expression2

Is any valid expression.

The data type of expression1 or expression2. For more information, see Integration Services Data Types.

expression1 and expression2 must evaluate to valid data types.

If expression1 and expression2 have the same data type, the result is that data type. All SSIS data types are supported. The following additional rules apply:

  • The DT_TEXT data type requires that expression1 and expression2 have the same code page.
  • The length of a result with the DT_BYTES data type is the length of the longer argument.

If expression1 and expression2 have different data types, the following rules apply:

The expression set, expression1 and expression2, must follow one of these rules:

  • Numeric. Both expression1 and expression2 must be a numeric data type. The intersection of the data types must be a numeric data type as specified in the rules about the implicit numeric conversions that the expression evaluator performs. The intersection of the two numeric data types cannot be null. For more information, see Data Type Conversion in Expressions.
  • String. Both expression1 and expression2 must be a string data type: DT_STR or DT_WSTR. The two expressions can evaluate to different string data types. The result has the DT_WSTR data type with a length of the longer argument.
  • Date. Both expression1 and expression2 must evaluate to one of the date data types: DT_DBDATE, DT_DATE, DT_DBTIME, DT_DBTIMESTAMP, or DT_FILETIME. The two expressions can evaluate to different date data types. The result has the DT_DBTIMESTAMP data type.

If boolean_expression evaluates to null, the expression result is null. If a selected expression, either expression1 or expression2 is null, the result is null. If a selected expression is not null, but the one not selected is null, the result is the value of the selected expression.

This example shows an expression that conditionally evaluates to savannah or unknown.

@AnimalName == "Elephant"? "savannah": "unknown"

This example shows an expression that references a ListPrice column. ListPrice has the DT_CY data type. The expression conditionally multiplies ListPrice by .2 or .1.

ListPrice < 350.00 ? ListPrice * .2 : ListPrice * .1
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.