Expression Basics

Package expressions provide a straightforward way to express a computation. The expression can be as simple as the sum of two numbers, as mundane as parsing a string to remove prefixes, suffixes, and spaces, or as complex as determining the total miles to award frequent fliers based on their frequent flier plans. Complex expressions are combinations of ever simpler expressions, and simple expressions are combinations of functions, literals, casts, operators, variables, and columns.

For example, the expression 1 + @Count is a simple expression constructed from a combination of a literal, the number 1, the operator +, and the variable Count indicated by the @ character. This expression tells the Expression Evaluator to add the literal 1 and the value of the variable Count.

More complex expressions can be built up from simple expressions. For example, the following expression builds a string with the current date and then appends the .csv file extension to create a filename:

RIGHT("0" + (DT_STR, 2, 1253)  MONTH( GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1253) DAY( GETDATE()), 2) + (DT_STR, 4, 1253)  YEAR( GETDATE()  ) + ".csv" 

This expression gets the date using the GETDATE() function, and prepends a zero to the month and day parts of the date. This is so that if the month or day is one digit, it has a zero prefixed to make a string like "02" or "09." However, if the month is already a two-digit number, the prepended zero is ignored because the RIGHT() function only takes the last two characters. The year is always a four-digit number and is returned as is. All date parts are dates and so they need to be converted to strings with the DT_STR cast. Chapter 22, "Advanced Data Flow Transformations," covers casting in detail, but for now, you should understand that the Expression Evaluator strictly applies type to all operations. All values must be explicitly cast to the type expected by functions if the two differ, or when using operators between two different types, one of the values must be cast to the type of the other.

Expression Functions

Four types of functions are available in this release of Integration Services:

• Mathematical These include numeric functions and power functions.

• String These include functions for parsing, finding, casing, and arranging strings.

• Date/Time These include functions for getting whole dates or parts of dates.

• Null These are special functions for dealing with the value NULL.

Literals

Literals are actual values that you directly input into the expression. There are Boolean, numeric, and string literals. Numeric and string literals are things like the number 1036, the string "War and Peace," and the float 7.35. Boolean literals can be expressed as the nonquoted and noncase sensitive strings TRUE and FALSE.

Casts

Casts are a way to coerce values of one type into another type. A text string like "10-03-1963" is obviously a date to any human, but to a computer, it's just text. Casting allows you to convert such strings into dates, numbers into strings, strings into floats, TRUE to a Boolean, and other such operations.

The Expression Evaluator is type sensitive because it's tightly bound to the way we do things in the Data Flow Task and is designed to be efficient, performant, and predictable. Casts minimize the guesswork the Expression Evaluator needs to do when confronted with two differing types. This also makes it easier to read an expression and know exactly what the resulting value and type will be.

Operators

Expression operators can be categorized into the following types (with example):

• Simple arithmetic: * (Multiplication)

• Logical: || (Logical OR)

• Bitwise or Binary: ! (Unary logical NOT)

• Grouping: ( ) (Parentheses)

• String: + (String concatenation)

• Comparison: == (Equal to)

• Assignment: = (Only available in For Loop Container expressions)

Variables

Variables can be referenced in expressions as long as they are in scope. For more about variables and variable scope, see Chapter 12, "The Venerable Variable." Variables are set apart in expressions by prefixing them with the @ character. All expression operations, casts, and functions can be used with variables.

Columns

In the SQL Server 2005 release, two data flow components support expressions. The derived column and conditional split transforms operate on columns on a row-by-row basis. In these transforms, the expression for a given column is evaluated for each row and a resulting operation is performed. To use the value of a specific column in the current row in an expression, you reference the column name separated by square brackets like this: [Column Name].