So I am having trouble merging three tables into one. All sources are DB, and I am using the MERGE JOIN function. When I join my first two tables, using standard left inner join, it returns the exact same number of rows as in the left (and largest) table, which is what I expect.

Here's where my trouble begins. I then apply another sort on the newly created table because I have to use a different sort key, as none of the three tables have common columns. When I attempt the second merge, it will count the first ~50k rows from the sort, stop counting, and continue to insert more rows into the destination table. I end up with a great many more rows than from either of the original tables. The highest source table has 3.3 million, but letting it run for a few hours generated over 800 million rows! Here is what the data flow looks like:

enter image description here

I'm sure this is either a lack of personal understanding of what I'm doing, or I need to take a different approach in SSIS to eliminate this Cartesian product situation.

  • I guess I understood your situation correct. Did you check 'remove duplicate records' in sort component before you do merge? What is the join type(last join before destination) you are expecting? If you are doing inner join then you may expect multiple records since you might have multiple matched records. – user22545 Apr 15 '13 at 5:48
  • Removing the duplicates does solve my problem, but unfortunately I am using sort keys that inherently contain duplicates (I am using sales data for this process), so I lose most of my data with this method. Im beginning to think that I have these multiplications because on one of my Merge Joins both sort keys have duplicates in their respective tables. Would it be an idea to add a "sort_key" that has no duplicates (using IDENTITY property) and sort everything on that? – user2253884 Apr 15 '13 at 17:18
  • A lazy SSIS technique that's worked for me (for good or bad) After 15 minutes of trying to fix something that by all rights should be impossible, I'll often delete my components and transforms and start again. In your case, it may help get past some hidden mistake which causes your merge join to be essentially a cross join. – Michael J Swart Apr 16 '13 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.