How to use given column for date in KSCD

Topics: Issues 2: Using the Component (Design-Time)
Apr 7, 2011 at 8:56 PM

First of all.. thank you for saving all of us the work to make this on our own and now where near as fast as you have it running... well done..

I have two questions:  

 

1. My source table has a key called Source_Key.  It represents a unique key every time a file is loaded it get a new source_key.  This way I can back out data if needed.  For SCD I was thinking that in the DIM table , *that also has a Source_key column, how do I ignore the field and still have it populated if a new or updated row is created?

2. My source system produces a ASOFDATE which I would like to use as my "system" date for use in the Effective and Expiry columns.  So instead of using a system date and time I want to be able to feed in the "current" effective date for new fields and expires on deleted/updated etc... how do you suggest I do that with your tool?

 

-Ken

Coordinator
Apr 8, 2011 at 7:46 PM

1. You should probably mark it as an SCD2 column, I'd think.  If that doesn't fit (cause I'm sure you've tried that), then I'm not sure there's much that can be done.  You may want to look at the auditing capabilities included in the component.  They work a little differently - by "load" - but the same idea of wanting to "back out" changes later...

2. In order to do that, one such ASOFDATE has to apply to the whole batch.  If you retreive that date into an SSIS variable - then that's the variable you should identify to the component for the "now" or "today" variable (on the SCD 2 Date Handling tab).

Apr 8, 2011 at 7:59 PM

Thank you for the reply...

1.  I guess what I am looking for is a way to mark a column as "pass through" meaning you do not do anything but make it available to downstream.  If I marked it as ignore it was not available downstream.  The way I have to solve it now if to put the source_key in a var then derive a column after each of your outputs.. It would be much simpler to be able to mark the column as pass through so nothing is done to it at all ( I thought that was what ignore did).  Does that makes sense?

2.  I already have a PACKAGE variable but the only options in the "variable containing today's date:" is the System: vars?  What am I missing?

 

Great component buy the way.. I am beginning to test it with over 1 billion rows! 

 

-Ken

Coordinator
May 3, 2011 at 11:24 PM

Re #1 - The problem I have is squaring this circle:  Your dimension table has a row for X, it has a column marked as "passthrough", and isn't mapped to a Source column.  Source rows X and Y come in.  Existing row X matches up to a source row X, and whatever SCD stuff happens, the passthrough column gets the value that was in the ED.  Row Y isn't in the dimension yet though... and doesn't have any information about what should be in the passthrough column.  What do I do?

Re #2 - the "today's date" or "now" variables have to be DateTime typed variables...