Find matched records between two tables

• Oscar_Boots

  Old Hand

  Points: 362

  More actions

  November 30, 2020 at 10:36 am

  #3815076

  Hi, I haven't been able to find anywhere how to do or which transformation to use.

  I've seen Lookup but it only has one input, merge join but I could only get it to append...

  I'm using SSIS 2019 Community in VS with Windows 10.

  I've brought in two tables using flat file source nodes

  They both have a PK of TransID that has been sorted, I'm looking for the best next step.

  I've seen that you can go to the ToolBox items and check the Table compare option but I don't have that available.

  Can someone suggest a nice quick easy method to join 2 tables to a Transformation, link the TransID PKs & output a matched * not matched output??

  Thanks

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  November 30, 2020 at 11:02 am

  #3815087

  Merge Join is the way to do any sort of INNER/LEFT/RIGHT/FULL join in SSIS, the below should give you an idea how to do it in SSIS

  https://www.tutorialgateway.org/left-outer-join-in-ssis/#:~:text=SSIS%20Left%20Outer%20Join%20using%20Merge%20Join%20Transformation,Join%20in%20SSIS%20Using%20Merge%20Join%20Transformation%20.

   

  I do have to ask why the transformation has to be done in the process flow.  Why not do it as ELT (which is how I think 99% of things should be done really  ETL is rarely what is needed in many SSIS packages I look at, its all Extract, Load, Transform) where you load to the files into temp / holding tables, then use native T-SQL to query and produce the transformed extract.