Property expressions within the data flow are not immediately obvious. Whilst most pipeline or data flow components do support them for their properties, the expression is not generally exposed through the component user interface, neither is the Expressions property shown properties grid for the component. Property expressions are only available at the task level, so for those components that do support expressions, you need to look in the parent data flow task's property Expressions collection. This can be accessed through the Property Grid for the task. You need to select the task context, either by selecting the task on the Control Flow design sheet, or clicking on some blank background when in the Data Flow design sheet.
When building components it is up to the component developer to explicitly state that a property is available as an expression, by default they are not. Generally the rule is that if a property influences the buffer structure, or put simply the column layout, then it should not be exposed as an expression as this would allow you to break the component at runtime.
Conditional Operator
The conditional operator behaves like an immediate If function, but it is an operator rather than a function.
When values used in a conditional operator test contain NULL, in a similar manner to ANSI SQL behaviour, NULL cannot be evaluated. To demonstrate consider the following dataset:
ColumnA
ColumnB
ColumnC
AA
bb
CC
NULL
bb
CC
AA
NULL
CC
AA
bb
NULL
AE
EE
EE
You can now use a Derived Column to run some tests, for example we use two tests, adding new columns with the Derived Column. For simplicity all columns are DT_WSTR(50).
Our resulting dataset shows than if either of columns B or C, the two used within the test itself, are NULL, the result is always NULL, regardless of the results specified in the conditional operator.
ColumnA
ColumnB
ColumnC
Test 1
Test 2
AA
bb
CC
B-C
AA
NULL
bb
CC
B-C
NULL
AA
NULL
CC
NULL
NULL
AA
bb
NULL
NULL
NULL
AE
EE
EE
AE
B-C
If you do encounter NULL values in the tested data, consider using the ISNULL function to check for this first, you can have multiple clauses in the test, just use the logical operators Or||, And&&&.
DT_GUID Data Type
The data type "DT_GUID" cannot be used with binary operator "=".
The equality or inequality operators do not support the Guid type, so you cannot test DT_GUID values. You can however easily cast the Guid to a string and then perform the comparsison.
