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 6:01 PM

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

wrote Jul 26, 2010 at 1:11 PM

toddmcdermid wrote Jul 31, 2010 at 2:09 AM

wrote Jul 31, 2010 at 2:10 AM

wrote Aug 19, 2010 at 3:43 PM

wrote Aug 22, 2010 at 1:04 AM

wrote Feb 21, 2013 at 11:28 PM

wrote May 16, 2013 at 10:51 AM

wrote May 16, 2013 at 10:51 AM

wrote Jun 14, 2013 at 7:26 AM

SameeraN wrote Sep 10, 2013 at 6: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