Do you know if there is any option to implement OR condition with Merge Join in SSIS? NO, there is no such option (Let me know if there is a way and I am not aware of it)!
Recently I come across a situation wherein I had to use OR condition in the Merge Join but there is no such provision in SSIS. Though the package is complex the requirement was simple to handle by modifying the SQL Query!
Transferring School_Facility table with 40+ columns from one of Oracle Server to SQL Server with multiple transformations.
Merge Join is used to join Fact.STATE = Dimension.STATE_CODE
During the analysis, multiple records are identified with STATE_NAME instead of STATE_CODE in Source.
New Requirement is Fact.STATE = Dimension.STATE_CODE OR Fact.STATE = Dimension.STATE_NAME
Implement the change with minimal changes to existing package and fix it ASAP.
After multiple attempts and experiments with package, just updated Dimension source query as shown below!
-- Existing Dimension query select STATE_ID, STATE_CODE from DEstination..STATES_TABLE -- Updated Dimension query select STATE_ID, CONVERT(nvarchar(512),STATE_CODE) AS STATE_CODE from DEstination..STATES_TABLE UNION select STATE_ID, STATE_NAME AS STATE_CODE from DEstination..STATES_TABLE --