what transform sequence to perform WHEN MATCHED, WHEN NOT MATCHED

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  December 29, 2014 at 5:22 am

  #296258

  i have a source dataset in which multiple criteria have to match to the destination oledb table before I can determine a MATCH or NO MATCH.

  the sql equivalent of what I want to accomplish is below. Please see that property_id AND care_type_id AND property_room_type_id all have to match with destination table on the row in addition to other assessments before deciding whether there's a match or not.

  WHEN MATCHED AND t.amount <> src.amount AND src.amount >0 AND src.property_id <> 0

  AND src.property_id = t.property_id

  AND src.care_type_id = t.care_type_id

  AND src.property_room_type_ID = t.property_room_type_id

  THEN UPDATE SET

  t.amount = src.amount,

  t.negotiable = src.negotiable,

  t.updated_by = src.updated_by,

  t.updated_on = src.updated_on

  WHEN NOT MATCHED AND src.property_id <> 0 THEN

  INSERT (property_id, property_room_type_id, care_type_id, amount, negotiable, created_by, created_on, updated_by, updated_on)

  VALUES(

  src.property_id,

  src.property_room_type_id,

  src.care_type_id,

  src.amount,

  src.negotiable,

  src.created_by,

  src.created_on,

  src.updated_by,

  src.updated_on)

  Lookup transform allows only one join and so I don't know how to do this in SSIS.

  Which ssis transforms (or sequence of transforms) will allow me to make join the source and destination datasets on multiple criteria before determining MATCH/NO MATCH?

  Thanks in advance.

• pietlinden

  SSC Guru

  Points: 63509

  More actions

  December 29, 2014 at 3:11 pm

  #1766874

  Can you use Conditional Split to divide the dataset -- so that you get the match/no match and then handle there?

  Katie and Emil are really good at this kind of thing... here's a video

  Emil's video on Conditional Split