3

Have a problem into a Data flow task, in SSIS.

In my current flow, I want to add some additional data, using a lookup object. If there is a match then to add the data based on the settings from the Lookup object and for no match to keep the process unchanged.

I set my lookup object to redirect the not matching records to No Match output and linked it to the existing flow (as I don't want to change the existing records, just to enhance with some additional data), but I don't know how link the Match output to the same flow.

If I was to use a TSql statement I've just used left join, but I just can't figure what other objects from SSIS to use, in order to have the existing flow unchanged, just to enhance it, when there's a match, with the new data.

Hadi
  • 36,233
  • 13
  • 65
  • 124
BogdanM
  • 957
  • 4
  • 15
  • 32
  • What would happen, for my current flow, if I set my setting for the Lookup object to just ignore the records with no match? Will the records still go through the LookUp object? – BogdanM Apr 11 '17 at 11:04

2 Answers2

1

You can do it without using of lookup redirect output.

Just configure Lookup to ignore No Match error so the output value will be set to Null for non matched value.

After doing this, add a Multicast Component after the Lookup , so you have multiplied your output then add a Conditional Split in one of The MultiCast outputs and filter Rows where ISNULL([YourColumn]) == True (You will get only No Match Rows in one output and all rows in the other)

Hadi
  • 36,233
  • 13
  • 65
  • 124
1

You can use a Merge join component for that.

You need to sort your data (can be done with a sort component) first. After that, just connect the two components with the Merge join component.

In the merge join, you can select which kind of join you want (check the link above for more about that). Works just like a T-SQL statement

DenStudent
  • 906
  • 1
  • 13
  • 37