July 9, 2008 at 12:05 am
I have a problem with the SCD’s in SSIS.
I would have thought that SCD’s do a sorta checksum to see if anything has changed, but it seems all data whether type 2 or type 1 gets updated everyday. Im also having an issue now where the SCD is inserting duplicates into the table and it shouldn’t be. I have defined my business keys which are unique.
Should I be putting in a checksum?
Has anyone else had problems with SCD’s in 2005? Where can i find out exactly how the SCD works?
July 9, 2008 at 5:52 am
The Slowly Changing Dimension transormation does not update data that has not changed. I have found it to be very effective on tables with less than 1000 records. It actually checks each field that you have told it to and determines if the value has changed.
Here is the catch - it, like everything else in SSIS, is case-sensitive, type-sensitive, and accent sensitive. It is a .net value comparison. So, if you have a source data flow with a CHAR(10) in it and your destination table has a VARCHAR(10) in it, your values will be different unless you actually have 10 characters in the field. Trailing spaces are seen as a difference.
My suggestion to you is that you go back and verify all of your data types, trim off all leading and trailing spaces that should not be there, and make sure you are not comparing UNICODE and non-UNICODE values.
A good check for the SCD wizard is to run it twice once you have it set up. It should see no changes the second time you run it - if you see changed or inserted values and nothing changed at the source, you have probably messed up some data types.
July 21, 2008 at 7:55 am
Hi Michael thanks so much for your response, very informative, What is accent sensitive? Type sensitive im assuming is datatype sensitive?
Ive changed all the fields to be the same size and type, im still getting duplicates coming through very frustrating!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply