Use another timestamp for effective and expiry date

Topics: Issues 2: Using the Component (Design-Time)
Jul 30, 2010 at 5:28 PM


I discover the kimball SCD for loading some dimension table wich i load from past. So Effective and expiry date are not from today but fom field in my source system.

But this field can't be mapping with my existing dimension, so i can't have this field in my output in order to use it in derivate column before writing expired SCD2 or New SCD2....


How can i use my source system field for loading this dimension table time stamp ?




Jul 31, 2010 at 2:58 AM

If I understand correctly, you are attempting to load your dimension table with "snapshots" of what the source system "used to look like" at various times in the past.  In effect, you have collected multiple snapshots of your source system, and you are loading this history into your dimension table by processing these snapshots one at a time through an SSIS package.

In order to do this properly, you need to only load one day's information per run of the data flow.  If you have multiple dates worth of snapshots in your source data, you will need to divide the records into batches - one batch per day - and use a Foreach Loop to load one day at a time.  You will need to take the column that contains your date and read that into an SSIS datetime variable that you can pass to the KSCD.  Using a Foreach Loop will do this naturally.

Aug 3, 2010 at 10:40 AM

Thanks for explanation.

I already had an timestamp variable but not in good format... Now i can use it for today value.


Thanks for the quick answer and component.


Sep 1, 2010 at 10:28 AM

In order to follow my dev and get sometimes data  from the past, i have a new problem.


When i create for the first time a member, Start Date IS NULL, End Date IS NULL and flag is active. Next day, i take day's data and this member change for SCD2, so i create a new line with start date at current day, end date IS NULL and flag IS active. First row for this member is now with flag inactive and EndDate at yesterday.

The problem is : we are the 2010/08/30, i have 2 rows for this member as describe before. I now load data from 2010/08/20.

So i need, to create a new line with start date NULL, End Date with min values from other startdate for this member, flag inactive. I need update only next date member with a new start date.


Without make new lookup after output New SCD2 and Expired SCD2, i have no big idea. the data flow doesn't contains the data from row from wich the update is.


Thanks for your help.



Sep 1, 2010 at 8:03 PM

I'm sorry - but the component is not designed to function in scenarios where you're loading dimensions in reverse or undetermined chronological order.  It will not "insert" changes chronologically.

The only way to "retroactively" manage SCDs is to roll your own logic.