Datasheet

As a review of the Lookup Transformation Editor, the first tab tells the component where to find the
reference (lookup) data. The second tab tells the component which fields it should try to match in the
source and reference data sets— the lookup column is the value you want to retrieve. Finally, the error
output configuration tells the Lookup what to do with the row in the case of failure.
What this transform does for you now that it is completely configured is try to find the name of the
model based on where the
ProductModelID on the source matches the reference table. If it can’t find a
match, an error occurs. However, you have configured the transform to ignore the error, and so the
ModelName column will now contain a NULL value if the match is not found. You could have also told
the transform to redirect rows where it can’t find a match to another output, which would be the red
arrow coming out of the transform. At that point, you could have written those rows to a queue, or
performed some additional cleanup.
Drag a Derived Column transform onto the data flow and connect the output of the Lookup transform
to the Derived Column. Name the Derived Column transform Assign Default Values and Price. In this
scenario, you want to give a special customer discount on products that your company makes (the
MakeFlag column lets you know if you made the product). There are two ways to perform this condi-
tional assignment:
❑ Use a Conditional Split transformation and then a Derived Column transformation
❑ Use the Derived Column transformation and utilize the SSIS expression language to perform
conditional coding
Open the Derived Column Transformation Editor (shown in Figure 1-8) in the data flow to configure the
transform. For the first derived column name, you will want to replace the
NULL values in ModelName
with the word Unknown. You will want this to replace the existing column of ModelName by selecting
Replace ‘ModelName’ from the Derived Column drop-down box. To solve a business requirement of
not having
NULLs in your warehouse, you can use the conditional operator of a question mark as shown
here for the
Expression column:
ISNULL([ModelName]) == TRUE ? “Unknown” : [ModelName]
Another entry in the Derived Column Transformation Editor will satisfy requirements for the discounted
product price for products that you make. Again, you will want to change the Derived Column drop-
down box to
Replace ‘ListPrice’. This time, the conditional logic will be slightly different. You’re
going to give a 10 percent discount on any product that you make, so you’ll read the
MakeFlag column. If
it’s set to
true, then you’ll discount. Otherwise, you’ll use the existing price in the column. The code will
look like this:
[MakeFlag] == TRUE ? [ListPrice] * 0.9 : [ListPrice]
In some destination table loading (and definitely in data warehouse dimension table
loading), if you can’t find a matching record in the Lookup, then an “unknown value”
needs to be inserted (a
NULL value in many cases is not acceptable). A more elegant
way to do this is through ignoring errors and using a derived column transform to
replace
NULLs with a default value.
12
Chapter 1: Getting Started
04_134115 ch01.qxp 4/24/07 6:40 PM Page 12