Tutorial :SSIS Look up - ignore certain records



Question:

I am doing an SSIS look up transformation, looking up in a voyages table, however some of my records don't have voyages, so I get errors. Is there any way that I can skip the look up on those records.


Solution:1

To expand on unclepaul84's answer, you can configure your lookup component to perform one of three actions on a failed lookup.

  1. Fail Component (the default and the action you have now from your question. Fails the job step (and maybe the entire package) when there are no matches to the row in a lookup attempt.)
  2. Ignore Failure (Doesn't fail your job step, leaves a null in the field you brought in from the lookup i.e. Voyage name? )
  3. Redirect Row (Doesn't fail your job step, allows you to direct rows with no voyage to a different processing flow for handling (i.e. if you want to put a default 'No Voyages' message in your Voyage Name field))

Alternatively, as John Saunders mentioned in his comment, you could test the VoyageID column and split your data flow into two paths depending upon if the VoyageID column is null. Since the Lookup component can handle this, I prefer using the single lookup rather than a conditional split followed by a lookup on one of the paths.


Solution:2

You could tell the lookup of component to ignore lookup failures.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »