Thursday, August 28, 2008

Let the Null Values pass through the Lookup transformation in SSIS

After the summer, here I am to communicate something that surprises me and it can be useful for someone (I hope).
When we use the lookup component to retrieve values from another OLEDB source, we always hope that all entries will match with a row in the lookup table. When this situation doesn't happen, we have the ability to let the row pass the lookup component with a null value in the lookup fields.
But what if we want to have a different behavior for the null values that come from the source pipeline? We can have the next situation:
We are translating some category codes from the products source table to a common set of new category codes. We have a translation table that will match every category source code with a new one. But we know that some rows from the source system come with a null value in the category field. For these rows, we want a 'Not Available' as translated value.
The only thing that we should do to get this behavior in the SSIS Lookup component is to perform this little trick into the lookup component statement:


select code, translation from CodeTable
union all
select null, 'Not Available'


As lookup component is able to match null values, the rows with a null value in the category field will be translated into the 'Not Available' value, just as we wanted.