If you are interested, Andy Leonard has a good write up on set based updates here:

http://sqlblog.com/blogs/andy_leonard/archive/2007/11/18/ssis-design-pattern-etl-instrumentation-part-3.aspx#SetBasedUpdates

You can ofcourse create your own set based SCD out of stock SSIS components or use one of the custom SCD components available on the interwebs.

You probably want to run something like this against each of the fact tables:

UPDATE F

SET

F.<Product_SKey> = '<Required_Surrogate_Key_Value>'

FROM <F_FactTableName> F

INNER JOIN

(SELECT <Product_SKey>

FROM <D_ProductDimension>...

Curious, may I ask what was causing the duplicates?

Anyway, like I said before, deleting the duplicates should be a case of first updating the fact table to reflect the desired...

This is a duplicate post.

See reply here: http://www.sqlservercentral.com/Forums/Topic1134716-391-1.aspx

It sounds like you've got yourself a hairy problem.

A few questions I'd be asking to start off with are:

- Is this a new DWH, or an existing one? Why has...

I had a similar issue with space characters, turns out not all space characters are created equal.

You can take the approach described here, using character codes:

H4K (9/9/2010)

---

Or you can also...

It all depends on your requirements, probably any of the articles Steve provided will serve your requirements, but it can be done in SSIS as well.

The approach would require initially...

SSIS 2008 has changed the order in which configurations are applied.

I've read a better description of configuration precedents but I can't find it now, so here's another one:

http://dougbert.com/blogs/dougbert/archive/2009/04/07/understand-how-ssis-package-configurations-are-applied.aspx

Anyway, in SSIS...

Correct, that's the simplest way to do it.

Ok, after some more investigation:

Transactions(DTC): ON

ValidateExternalMetada: FALSE

Primary key on destination.

I can load up to 9892 rows with no problem.

SELECT TOP 9892 A,B

FROM X

ORDER BY A,B

Succeeds.

SELECT TOP 9893 A,B...

Update:

We finally got the source system data cleaned up and there is no longer invalid data but loading the destination table with the transactions on and validation off still failed.

With...

sam.dahl (3/2/2011)

---

I have two more things to try when I get to work tomorrow:

1. leave transactions off whilst setting validate external metadata as false and seeing what effect that has...

Yeah, that's where I started.

I initially had transactions managed via DTC then I found that I couldn't get the truncate update process to work without causing a lock (unless I...

That's the thing. It's the primary key in the source and it should remain as such in the destination. The entry with leading spaces is invalid and we are...