question

poy avatar image
poy asked

SSIS - Merge Join data loading error

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!

ssisjoinsmerge
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Do you have a sort transformation after each source? SSIS works better with the explicit sort transformation rather than selecting the sort column from the source.

Also... SSIS processes those joins using caSE-sensitivity and also honors trailing spaces.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image Rob Farley commented ·
You don't need the Sort operator. You need to set the IsSorted property on the source output. You do this in Advanced Properties.
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
You can use the IsSorted and that works sometimes - not all. To ensure the sort is correct you should use the Sort operator.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.