Insert or Update SSIS for Composite Primary Key

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

Jeagant

I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.

Hopes this help.

Peter Avenant

|||

Hi Peter ,

Thanks for the suggestion .but my source table does not have surrogate key.

Jegan

|||

Jegan,

What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).

Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.

Rafael Salas

|||

Hi Jegan,

Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.

Here is what you need to set in each page of the wizard:

In the first page you will need to set c1,c2, etc as your "business keys"|||

Tom,

Thats Great Thanks for the suggestion

Jegan

|||

Hi Peter,

The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.

Checking for both checksum and binary_checksum does not resolve the problem.

MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.

Thanks

|||

Linkies,

As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca

Jakes