Home > SSIS Best Practices > SSIS #96 – Everybody Needs Integration Services Expression Reference

SSIS #96 – Everybody Needs Integration Services Expression Reference

In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.

In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.

If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.

OK, here is the official link to the Integration Services Expression Reference on MSDN.

So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.

Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:

Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.

Did you notice that it  says expressions are complex frequently?

There are 4 topics on the MSDN reference site.

Topic

Description

Integration Services Expression Concepts

Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.

Operators (SSIS Expression)

Describes the operators that the expression evaluator provides.

String Functions and Other Functions (SSIS Expression)

Describes the functions that the expression evaluator provides.

Advanced Integration Services Expressions

Provides expressions that use multiple operators and functions.

The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.

So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.

Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.

image 

Here is a “simple” expression ( simple only after I remembered AND needed to be &&):

@varHolidayInd == "N" && @varWeekendInd == "N"

image

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: