Now I know I'm not the only one having this issue. Two issues actually.
1. When a column changes and is recognized in the History Preserving transform, the records Active flag is set to 0 (which is fine, that's what I want) but it's Effective_End_Date is somehow changed to the same value of its Effective_From_Date.
2. When the row from Issue 1 is updated, the new Inserted row (the new active row for that key) is inserted with an Effective_From_Date of the previous records row. WHAT? Why does it do this? Why doesn't it set the Effective_From_Date to the current system time?
I'm sure that both issues are related and fixing one will fix the other. Buy why is it doing this????
Thanks Suneer! Your answer to number 2 sounds like it's assuming that I'm passing in a column that is populated already for EffectiveFromDate and EffectiveToDate, is that correct? How does History Preserving handle type 1 changes...my guess is that it doesn't at all which means a flow split to handle type 1's and type 2's, then merging them down stream...
1) In History Preservation, there is an option to set - "Use Valid From Date of New Record". If you set this, Effective End Date of the old record will be updated with the Effective From Date of the New Record (with same key value). You can also use 'One day before effective end date of new record'
2) For a newly inserted row, Effective From Date will be what you pass as input. It is not the Effective From Date of newly inserted row which is changing, but the Effective End Date of the updated row (the previous row with the same key value), which changes as per the Effective From Date of the new row, due to the above mentioned option.