SSIS – MERGE JOIN with OR condition

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!

SSIS_OR-Merge_Join

As-IS:
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

Requirement:
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.

Fix:
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
--
Advertisements
This entry was posted in knowledge, Productivity, SQL Query, SQL Server, SSIS and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s