How the SCD handles the data deleted in SRC side?

Question

Hello Friends,

Here comes one question about how to delete the rows in destination table if these rows were deleted in source side.

Seems to me the SCD component can not facilitate this. is this true?

How to solve this? TSQL? if source and destination tables are in two server, TSQL may not make sense.

Any good suggestion?

---

Derek

Answer 1

I would recommend you to not use "out of the box" SCD component, but use one written by Todd: http://dimensionmergescd.codeplex.com/.

It supports delete scenario.

Answer 2

What you should do is I select from your source data as you already do and use a second source component to select from your destination. Then use a merge join component (both inputs will need to be sorted on the same columns) and set the join type to FULL OUTER JOIN.

drag the output of the merge join component to a conditional split and the logic here is that if the column you used for the full outer join is null on the side of your destination data, it is then a row that needs to be added, if it is null on the side of your source data, it is then a row that needs to be deleted and finally, the last option is not null value on both sides (the default output from your conditional split) then those are rows that need to be updated.

of course, using this approach, it is possible that rows in the default output of the conditional split (rows to be updated) contain rows that have no difference from the destination. You can build logic to detect that or you can decide to update them anyway.

Of course, your source data needs to be a full refresh as opposed to an incremental load.

Hope it helps!

Answer 3

That limitation is true.  Investigate the suggestions made by the other posters.

---

Talk to me now on

Answer 4

I am doing so. But it looks a very simple issue causes some complicates.

Thanks for your contribution.

---

Derek