CODEWITHSUNDEEP
sql-serverssissql-server-2012ssis-2012scd
Neil P
Neil P

Reputation: 3190

SSIS 2012 - setting slowly changing dimension UpdateChangingAttributeHistory to true

Using the SCD task wizard, I am unable to set the UpdateChangingAttributeHistory property and it defaults to false. I am able to set this to true using the advanced editor, but this does not update the generated flow and therefore the behavior has not changed. What do I need to do to trigger SSIS to regenerate the flow without re-running the wizard (which sets UpdateChangingAttributeHistory back to false)?

My goal is to have a type 2 scd that updates the fact table when a change occurs.

Upvotes: 0

Views: 708

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

I can't explain why the UpdateChangingAttributeHistory checkbox is greyed out in the wizard - that sounds bad. But I can tell you what it does. If this is False, the default, it will change the WHERE clause of the OLE DB Command for the "Changing Attributes Updates Output" to something like this:

... WHERE [SomeKeyColumn] = ? AND [SCD_EndDate] IS NULL

If you are not using dates, it will be something like [CurrentRowFlag] = 'Y'

If you set the attribute to true, it removes that part of the WHERE clause so that all rows with Type 1 changes will be updated, current and historical. And that's it. This is why, if you do not regenerate the component, the flag has no effect, because the OLE DB command did not change.

So you can manually make the change in the Changing Attributes Updates Output, but those changes might be lost if the SCD component is regenerated in the future.
As noted in the comments, it would be worth considering creating this functionality with other components to improve performance and maintainability, especially if it is acting funny to begin with.

Upvotes: 1

Related Questions