Data Flow Property Expressions
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.
Test ? True Result : False Result
(@[User::Variable] > 1000 ? "Big Number" : "Small Number")
NULL Tests
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).
| Derived Column Name | Derived Column | Expression |
| Test1 | <add as new column> | ColumnB != ColumnC ? "B-C" : ColumnA |
| Test2 | <add as new column> | ColumnB == ColumnC ? "B-C" : ColumnA |
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 | Test1 | Test2 |
| 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.
(DT_WSTR,38)[GuidColumn] = (DT_WSTR,38)[GuidColumnNew]
DT_STR Codepages
The DT_STR type requires a codepage, see http://www.microsoft.com/globaldev/reference/WinCP.mspx for a full list.