Referencing variables is easy using the Expression Builder. Drag and drop variables onto the Expression Builder to format the variable into the expression properly. As shown in the following example, notice that the format of the variable automatically dropped into the expression is preceded with an @ symbol, followed by the namespace, a C++-like scope resolution operator, and then the variable name:
Technically, if the variable is not repeated in multiple namespaces and there are no special characters (including spaces) in the variable name, you could get away with referring to the variable using a short identifier like@variablename or just the variable name. However, this type of lazy variable referencing can get you into trouble later. We recommend that you stick with the fully qualified way of referencing variables in all SSIS expressions.
Referencing parameters is just as simple in the Expression Builder. When you drag and drop the parameter name, the value is automatically preceded with an @ symbol, followed by square brackets containing a dollar sign, the namespace of the package or project, the C++-like scope resolution operator, and the parameter name:
Typically, developers can run into trouble with variable and parameter references in the Precedence Constraint Editor (refer to Figure 5-10). That’s probably because there is no Expression Builder to help build the expression, so it must be manually entered. This is where the tip of creating the dummy variable MyExpressionTester comes in handy. You can create an expression within this dummy variable Expression Builder and then simply cut and paste the value into the Precedence Constraint Editor.
Columns can be referenced in expressions, but only within components in a Data Flow task. This makes sense. Creating a global expression to reference a value in a Data Flow is the equivalent of trying to use a single variable to capture the value of a set-based processing operation. Even a variable expression defined at the same level or scope of a Data Flow task should not be able to reference a single column in the Data Flow under constant change. However, from within specific components like the Derived Column Transformation, the Expression Builder can reference a column because operations occur at the row level. Expressions within a data component can access column identifiers to allow point-and-click building of expressions.There are a couple things to remember when referencing columns in expressions:
- Data Flow column names must follow the SSIS standards for specialcharacters.
- Column names must be uniquely named or qualified within a Data Flow.
A common issue with building expressions referencing columns in a Data Flow has less to do with the expression language than the names of the columns themselves. This is particularly true when dealing with Microsoft Excel or Access data, where columns can use nonstandard naming conventions. SSIS requires that the columns being used in an expression begin with either a valid Unicode letter or an underscore (_). With the exception of bracket characters, any other special characters require qualification of the column in order to be used within an expression.
Brackets ([ and ]) are the designators used by SSIS to qualify a column name. Qualification of column names is required if the name contains special characters — including spaces. Because bracket characters are column name qualifiers, any column with brackets in the name must be renamed to use an expression. This doesn’t require changing the column name in the originating source. Column names also must be qualified when two or more columns in a Data Flow have the same name, in order to avoid ambiguous references. The following are examples of columns that need qualification:
Another way to refer to columns, unique to SSIS, is by lineage number. A lineage number is something that SSIS assigns to each input and output as it is added to a transformation component in a package. The lineage number is quickly replaced by the real column name when the expression is syntax compiled. To find the lineage number for a column, look at any advanced editor dialog and find the column in the input column properties under LineageID. Keep in mind that as you add columns, the lineage numbers may change, so they should not be used for manipulation purposes, only for troubleshooting.