Table comparrison creating inserts instead of updates

dataservices job design
1

I am using BODI 11.7.3 on SQL Server 2005.

I have a dataflow that has a table comparison transform in it.

If I use sorted-input then everything that goes through that tranform is made an insert.

If I use row-by-row select then it correctly determines which rows are inserts and which rows are updates.

I have double checked that the input is sorted correctly and that the table has a clustered primary key.

Why else would Sorted input fail like this?

bungy :us: (BOB member since 2007-10-04)

2

this is due to differences in collation order between DI and SQL Server - there are many other threads on this subject on here

speed12 (BOB member since 2004-09-27)

3

but that should only apply if the primary key is an alphanumeric.

my primary key is made up of 3 columns, each of which is an integer.

bungy :us: (BOB member since 2007-10-04)