5

Resolved

Deleted Output vs. Expired SCD2 Output

description

Hi Todd!Many thanks for your reply. I tried various ways to get rows in DELETED output definitely. Actually I use you component in the same way as tcash wrote you before http://kimballscd.codeplex.com/Thread/View.aspx?ThreadId=208962 just working with smaller fact tables. It's really extremly fast component!I've two databases named Source and Destination and one table in each database. In Source database
 
 
CREATE TABLE [DimensionSrc1](
[ID] [bigint] NOT NULL,
[Field1_varchar] [varchar](20) NULL,
[Field2_money] [money] NULL,
[Field3_forkey_bigint] [bigint] NULL,
CONSTRAINT [PK_Dimention1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
and in Destination database
 
 
CREATE TABLE [DimensionDW1](
[DimentionKey] [bigint] NOT NULL,
[AlternateKey] [bigint] NULL,
[DateEffective] [datetime] NULL,
[DateExpires] [datetime] NULL,
[Field1_varchar] [varchar](20) NULL,
[Field2_money] [money] NULL,
[Field3_forkey_bigint] [bigint] NULL,
[IsCurrent] [bit] NULL
) ON [PRIMARY]
 
 
To fill tables with sample data you can use the following scripts
 
 
INSERT INTO [DimensionSrc1]
VALUES (200001,'somevalue1', 10000, 100001),
    (200002,'somevalue2', 20000, 100002),
    (200003,'somevalue3', 30000, 100003),
    (200004,'somevalue4', 40000, 100004),
    (200005,'somevalue5', 50000, 100005),
    (200007,'somevalue7', 70000, 100007),
    (200008,'somevalue8', 80000, 100008),
    (200009,'somevalue9', 90000, 100009),
    (200010,'somevalue10', 100000, 100010)
 
 
 
INSERT INTO [DimensionDW1](
[DimentionKey],
[AlternateKey],
[Field1_varchar],
[Field2_money],
[Field3_forkey_bigint]
)
VALUES (1,200008,'somevalue8',80000,100008),
    (2,200004,'somevalue4',40000,100004),
    (3,200010,'somevalue10',100000,100010),
    (4,200007,'somevalue7',70000,100007),
    (5,200002,'somevalue2',20000,100002),
    (6,200006,'somevalue6',60000,100006),
    (7,200009,'somevalue9',90000,100009),
    (8,200001,'somevalue1',10000,100001),
    (9,200003,'somevalue3',30000,100003),
    (10,200005,'somevalue5',50000,100005)
 
 
As you can see there are 9 rows in source dimension table and 10 rows in destination dimension table. As you wrote above I marked housekeeping fields as "not used". After execution the package I have all 10 rows in UNCHANGED output and no rows in DELETED one.
 
Thanks,
 
Alex

comments

danelliott wrote Jun 18, 2010 at 7:01 PM

Looking at the source code, ProcessCache_SendExistingToDeleted() only gets called when _runtimeIsSCD2Enabled is true. There needs to be another conditional test here.

toddmcdermid wrote Jul 31, 2010 at 3:09 AM

SameeraN wrote Sep 10, 2013 at 7:31 PM

It seems that this is not fixed. I am using KSCD 1.6. Deleted records in the source are not being directed to deleted output.
Did any one find that this is the case, or is it really fixed, in which case I might have not configured the componnet correctly.

Thanks