Slowly Changing Dimension Issue

Question

Slowly Changing Dimension Issue

ehlinger

Hall of Fame

Points: 3585
More actions
July 31, 2008 at 4:03 pm

#125334

I attempted my first SCD in SSIS today and am having an issue that is giving me fits. I used the SCD wizard and set up everything. I have a combination of Type 1 and Type 2 changes and the package runs fine. However, when I run it a second time after not changing anything in the source table it goes ahead and puts a new record in for every record. In affect, duplicating all the records in my dimension.
I would expect it not to do anything the second time because nothing has changed. What am I missing?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

SSIS is very data type sensitive. My guess is that your source data is a different data type. It can be something as simple as your source is a VARCHAR(10) and your destination is a VARCHAR(12). It can also be trailing spaces or CASE. You need to go back and re-check all of your data types in the inputs and outputs in SSIS to make sure they are all exactly the same.

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

Thanks. That was exactly what the problem was. The source system had a lot of char fields and in the warehouse they were varchar. I just changed the view from the source system and casted the fields to the data type in the warehouse and all is good.

SSIS developer SSC Journeyman Points: 95 More actions · Answer 3

Hi,

My problem with SCD is the same as mentioned above. The SCD updates the Destination table even when there is no change in any of the columns. Also, I have checked for the datatypes in the source and the destination tables, and they are exactly the same.

Moreover, when I investigated the issue, I realized that the SCD behaves likes this only for columns having 'DATE' and 'CHAR' datatype. When I dont include the columns with datatype 'CHAR' and 'DATE' the SCD updates only the changed records. In short, the SCD behaves normally for only 'NVARCHAR' columns.

Could anyone please suggest what exactly is the SCD behaving in such a fashion? And how do I resolve this issue?

P.S.: We are using SQL Server 2008

Thanks in advance!