Expression Language Patterns - SQL Server Integration Services Design Patterns - page 298

One interesting caveat regarding the application of expressions within the data flow
is the way in which SSIS exposes component-level expressions. Although the expres-
sion language is very useful within the pipeline of the data flow, most components do
not actually expose properties that can be set using expressions. For those that do allow
expressions on certain properties, these expressions are surfaced as elements of the data
flow itself and will appear as part of the options in the Data Flow Properties window
while working in the control flow.
As shown in Figure 10-7 , you are using the expression properties of the data flow
to access the ADO.NET data source within that data flow. As you can see, the identifi-
er in the Property column shows that this expression belongs to the data source within
the data flow, allowing you to set the SqlCommand property of that source. It's useful
to note here that I used the ADO.NET source purposefully in this example. Since this
source does not currently allow the use of parameters, setting the SqlCommand prop-
erty is often an acceptable substitute for dynamically retrieving data from a relational
database using this component.
Figure 10-7 . Data flow expression
Application of Business Rules
Although they share some of the same methods, the applying business rules differs
conceptually from data cleansing. For the most part, data cleansing is considered to be
universal: misspelled words, inconsistent casing, extraneous spacing, and the NULL-
versus-blank-versus-zero quandary are all common problems that must be dealt with in
almost every ETL process. Business rules, on the other hand, are specific use cases in