SSIS Lookup Transformation

0
votes

I am working on a project with SSIS and would like to know if situation is possible to handle using the Lookup Transformation.

I have an input stream with values something like

  • NAME1
  • NAME2
  • NAME3
  • ALL

I want to lookup these values in a master table which has only names and NOT "ALL" value. But I want to pass the records with ALL too. How can I pass the lookup values for few known values where there will be no match?

If not lookup, please suggest me some other way.

2
ssis

2 Answers

0
votes

There is a NoMatchBehavior property in a Lookup component on how to handle rows without a match. You can set it with the editor (it's a dropdown on the General tab) or with the Properties window of the component.

For your scenario there are two options:

  • Ignore failure and keep them in the same stream. The lookup output column's values will all be NULL.
  • Redirect the no match rows into another stream. Again, the values will be NULL, but you can handle them differently if you'd like.
0
votes

In master table table insert NA Name with other attributes NA. When you lookup Set Ignore Failure on how to handle no match entries. Then you get NULL values for not matching Names, so insert Derived Column element and replace NULL values with your new PrimaryKey of NA Name.