Loading data via Entity Based Staging in SSIS how to update data with auto increment entities

Question

Hi All

I have written a SSIS Package to load data into my MDS System via Entity Baed Staging and it works for the first time but i have a problem with entities having auto increment.

How will the update work when i load data in entities having autoincrement. I have a ImportType of "0" which means when i do the update it from source it will update in MDS Ssystem but my MDS System have AutoIncrement Entities so everytime its going to load data it will be new Code ID.

How can i resolve this issue.

Thanks

Answer 1

Sachbat,

Create a subscription view in MDS (This inturn creates a database view). Use this view to lookup with business key and insert code into staging table. If there is no match on the lookup then insert the record with blank code into staging table.

For example: - Lets consider you are loading Product entity from a source. Firstly, create a subscription view for this Product entity (lets name it as SV_Product). In the DFT, after the oledb source transformation, place the lookup task. The lookup task will have SV_Product. Join on the ProductName from source and if the product name matches, insert corresponding code into staging table. If the join does not work, then insert the no match output records into staging table with blank or NULL codes.

Let me know if you need more details. Thanks.