SSIS Expressions

 

The language used to build expressions can be a bit disorienting at first. If you started out as a programmer, you will be comfortable switching between Visual Basic, C#, and T-SQL. The key to being proficient in building expressions is understanding that the syntax of this new scripting language is a combination of all these different languages.

Learn how to use SSIS, from beginner basics to advanced techniques. Enroll for Free "SSIS Training" Demo!

Expressions in SSIS

C#-Like? Close, but Not Completely

Why not write the expression language in T-SQL or a. NET-compliant language? The answer is mostly related to marketing: expressions should reflect the multiplatform capability to operating on more than just SQL Server databases. Remember that expressions can be used on data from other RDBMS sources, like Oracle, DB2, and even data from XML files. However, the technical explanation is that the SSIS and SQL Server core engines are written in native code, so any extension of the expression language to use .NET functions would incur the performance impact of loading the CLR and the memory management systems. The expression language without .NET integration can be optimized for the custom memory management required for pumping large row sets through Data Flow operations. As the SSIS product matures, you’ll see the SSIS team add more expression enhancements to expand on the existing functions. Meanwhile, let’s look at some of the pitfalls of using the expression language.

The expression language is marketed as having a heavily C#-like syntax, and for the most part that is true. However, you can’t just put on your C# hat and start working, because some peculiarities are mixed into the scripting language. The language is heavily C#-like when it comes to using logical and comparison operators, but it leans toward a Visual Basic flavor and sometimes a little T-SQL for functions. For example, notice that the following common operators are undeniably from a C# lineage:

The conditional operator may be new to you, but it is especially important for creating compound expressions. In earlier releases of SSIS, the availability of this operator wasn’t readily intuitive. If you aren’t used to this C-style ternary operator, it is equivalent to similar

The following functions look more like Visual Basic script or T-SQL language functions than C#:

This makes things interesting because you can’t just plug in a C# function without ensuring that there isn’t an SSIS expression function to perform the same operation that is named differently. However, if you make this type of mistake, don’t worry. Either the expression turns red, or you’ll immediately get a descriptive error instructing you that the function is not recognized upon attempting to save. A quick look in Books Online can help resolve these types of function syntax differences.

In some instances, the function you are looking for can be drastically different and cause some frustration. For example, if you are used to coding in C#, it may not be intuitive to look for the GETDATE() function to return the current date. The GETDATE() function is typically something one would expect from a T-SQL language construct. Thankfully, it performs like a T-SQL function should to return the current date. This is not always the case. Some functions look like T-SQL functions but behave differently:

This departure from the T-SQL standard can leave you scratching your head when the expression doesn’t compile. The biggest complaint about this function is that you have to use composite DATEPART() functions to get to any date part other than a month, day, or year. This is a common task for naming files for archiving. Nor does the ISNULL() function work like the T-SQL function. It returns either true or false to test a value for the existence of NULL. You can’t substitute a default value as you would in T-SQL.

Check Out SSIS Tutorials

These slight variations in the expression language between full-scale implementations of T-SQL, C#, or Visual Basic syntaxes do cause some initial confusion and frustration, but these differences are minor in the grand scheme of things. Later in Using Variables, Parameters, and Expressions Topic, you’ll find a list of expressions that you can cut and paste to emulate many of the functions that are not immediately available in the expression language.

SSIS Expression Builder

Several locations in the SSIS development environment allow the creation of an expression. Whether you are in the Variables window or within any property expression editor, ultimately the expression is created within a user interface called the Expression Builder. This user interface maintains easy references to both system- and user-based variables and provides access to expression functions and operators. The most important feature of the Expression Builder is the capability it provides to test an expression — that is, to see the evaluated value — by clicking the Evaluate Expression button. This is especially helpful as you learn the syntax of the expression language. By dragging and dropping variables and operators onto the expression workspace, you can see how to format expressions properly. Inside Data Flow components, typically a specific expression builder includes additional elements related to the Data Flow. In Figure 5-9, you can see that the user interface for the Derived Column Transformation includes a folder named Columns to allow expressions to be built with data from the Data Flow.

The only downside in the Data Flow component versions of the Expression Builder is that you don’t have the option to see the results of evaluating the expression to determine whether you coded the expression properly. The reason is that you can’t see the data from the Data Flow, because this information is not available without running the package.

Frequently Asked SSIS Interview Questions & Answers

This brings up a point about maintainability. If you have an involved expression that can be realized independently from data from the data stream, you should build the expression outside of the Data Flow component and simply plug it in as a variable. However, in some cases, you have no choice but to build the expression at the Data Flow component level. If so, one of the best practices that we recommend is to create one variable at the package level called MyExpressionTest. This variable gives you a quick jumping-off point to build and test expressions to ensure that the syntax is coded correctly. Simply access the Variables property window and click the ellipsis beside the expression property, and the Expression Builder pops up. Use this technique to experiment with some of the basic syntax of the expression language in the next section.

SSIS Syntax Basics

Building an expression in SSIS requires an understanding of the syntax details of the expression language. Each of the following sections dives into an aspect of the expression syntax and explores the typical issues encountered with it, including their resolution.

SSIS Equivalence Operator

This binary operator, which is used to compare two values, seems to create some problems for SSIS developers who are not used to using the double equal sign syntax (==). Forgetting to use the double equal sign in a comparison operation can produce head-scratching results. For example, consider a precedence operation that tests a variable value to determine whether the value is equal to True, but the expression is written with a single equal sign. Imagine that the variable is set by a previous script task that checks whether a file is available to process.

@[User::MyBooleanValue] = True

The expression is evaluated, and @MyBooleanValue is assigned the value of True. This overwrites any previous value for the variable. The precedence constraint succeeds, the value is true, and the tasks continue to run with a green light. If you aren’t used to using the double equal sign syntax, this will come back to bite you, which is why we have discussed this operator by itself at the front of the syntax section.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator