I'm using the Slowly Changing Dimension (SCD) component in SSIS to update
dimension tables in a data warehouse we are currently developing. I've been
running into problems with "some" of the dimensions having surrogate records
being created for type 2 changes that don't really exist. In other words,
the columns for which I have defined as a "Historical Change" type do not
have changes in the source data, but the SCD is still creating new records
for every inbound record.

In every case, I've been able to isolate the issue to a specific column by
turning off historical tracking on everything and then turning it back on one
at a time until I discover the column which appears to be causing the
generation of bogus surrogate keys. I've not yet been able to determine a
pattern, but was able to create a small sample which i've sent to Microsoft
for analysis. When I analyze the dimension records, I will get identical
BINARY_CHECKSUMs when reviewing the columns that have historical tracking on
them.

Has anyone else run into this problem or stumbled upon a solution?

Hello KS,

Ok so let's make sure the SCD is goofing here.

You can recreate the behaviour by using a few more components

A Merge Join
A Conditional Split

#You can recreate the SCD functionality snd if this works then you know the
SCD is not working properly


In the merge Join (Left Outer) match on keys

In the Conditional Split

You have this as the new output

ISNULL(ColumnInDimensionTable)

To do the changes you do similar to this

ColumnIn != ColumnDest || ColumnIn2 != ColumnDest2 etc

also check the metadata of the columns you are trying to match and manipulate
for changes. Maybe precision is getting skipped and it thinks it is a changed
row.


Make sense?



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com