1

this is a simple SCD with a type two (historic) change available.

In the image, when a row is updated, two distinct rows exist, one that travels down the 'New Output' path and one that travels down the 'Historical Attributes Inserts Output'.

Down the path of 'Historical Attributes Inserts Output', the 'Derived Column' adds a column (or replaces the column information) so that the RowisCurrent (for example) can be changed to False.

Down the 'New Output' path, the row picks up the RowisCurrent status of 'True' at 'Derived Column 1'.

What I do not understand is what purpose the 'Union All' serves. Why is there a connection between 'OLE DB Command' and the Union All?

If the expired rows are updated at the 'OLE DB Command', what is being passed through, and wouldn't whatever is passed through have the RowisCurrent set to 'True' at 'Derived Column 1' before it is written to the database at 'Insert Destination'?

enter image description here

ivan7707
  • 1,146
  • 1
  • 13
  • 24

1 Answers1

0

I think this is the answer.

Records going down the "New Output" path are records that have a new business key that does not exist in the destination table.

Records going down the "Historical Attributes Insert Output" path are those where the business key existed in the destination table, we now have to UPDATE the current flag indicator of the existing record to old, and THEN INSERT a new record with a current flag indicator of true.

user5901715
  • 23
  • 4