Expressions

by Darren Green 28 Oct 2008 08:40

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.

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS