Post reply

SSIS - Merge Join data loading error

  • March 1, 2010 at 2:41 pm

    #139140

    Hi,

    i have 2 data sources (i.e. 2 different tables) that i need to join and dumps the output to a destination table.

    table a consists of 5 records while table b consists of 5000 records. they are being joined by a uniqueidentifier ID and the result will be inserted to table c as the destination.

    I did the join by using the merge join tool (inner join) and did all the sorting steps but when I try to ran the data flow task, the step that runs the extraction from table a is finished first and then the OLE DB Destination [16447]: The final commit for the data insertion is ended before the extraction from table c has begun.

    any idea where's the error here?

    thanks a lot!

  • March 1, 2010 at 3:15 pm

    #1126752

    i suggest you use SQL statement instead to join the tables rather than using Merge join in SSIS... that will give you a better performance..

    second option would be to use Lookup... Use the Lookup for table that has 5 rows while source will have the SQL that results 50000 rows.. You can use the same column to lookup that you were using for join...

    If you still want to use merge join only.. i suggest you post the descriptive Error

  • March 1, 2010 at 3:52 pm

    #1126761

    Hi,

    thanks for the input.

    However, I can't use sql inner join query since these 2 tables are located in 2 different servers that are not linked.

    I'm also not allowed to create temp tables on any other data base. =(

    There's no error message. It's only that, it commits the insertion to the destination source task while the extraction of data from one table is still running - which should not be the case because the result will be 0 rows inserted.

  • March 1, 2010 at 4:01 pm

    #1126764

    Did you try using Lookup component as i have suggested...

  • March 1, 2010 at 4:33 pm

    #1126776

    Hi,

    I was able to use lookups and it worked fine.

    I was also able to solve the problem I had with merge joins.

    It seems that my data sources are not "pre-sorted" that's why I have to use the Sort task instead of directly setting the properties of " IsSorted" = true in the Advance Editor option. This way my data flow task worked the way it should be.

    I just need to know where and how to set the IsSorted properties of my data sources.

    Thanks a lot for the inputs! =)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply