problem with effective date values

Topics: General, Issues 2: Using the Component (Design-Time), Possible Bugs/Missing Features?
Apr 5, 2011 at 2:09 PM

While configuring Kimbal SCD transformation, I've faced up a problem with effective date values for new records. It's fixed value with date from 18th century. :)

What I need is one of these options: to use values from one source columns or to use today's date (with getdate() function and variable in ssis).

The problem is that I have only 4 options for setting 1st record's effective date:
- MS SQL datetime Min or Max
- MS SQL small datetime Min or Max
- Null
- Explicit date

I'm using MS SQL datetime Min or Max.

I would appreciate if you can give me any suggestion how to override this situation.

Thanks in advance!


Apr 8, 2011 at 6:35 PM

First, I'm going to recommend you upgrade to the just-released v1.6, because that's what I'm going to talk about :)

You no longer get that "first record's effective date" option in the component.  The more I thought about it, it didn't make sense.  New records (and new versions of records) now get the date/time contained in the "now" variable.  Hope that solves your problem for you...

Apr 8, 2011 at 10:39 PM
Edited Apr 8, 2011 at 10:40 PM

It was marked as Beta release two days ago, so I used v1.5.

I tried the latest version and everything works fine!


Aug 31, 2012 at 9:28 PM

Is there a way I can use the ModifiedOn date in my OLTP system as the EffectiveDate. I do not want to use the date when the Load Package is run.

I want to use ModifiedOn as the "now" variable and the variable value will change for each row I am processing.

Is there a way to do that?


Nov 1, 2012 at 5:31 PM

I have the same question.  I want to use a value from the source system to populate effective date.  Has anyone found a way to do that?

Feb 4, 2013 at 2:31 PM
I have the exact same issue, how to use a column for the "now" value ?
Dec 5, 2014 at 9:01 AM
I'm struggling with the same problem. How to use the modification date of the record (CreatedDate, UpdatedDate, Modified) from the source system?
I've tested a lot of options with no success, maybe a custom logic is the right answer, but it's a shame SSIS/Dimension merge/ doesn't support this natively! This is the only option when you're doing first time dimension load. The actual effective date is different for each row.
Pentaho does this so much better. I've started to hate SSIS.