0

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.

Neil P
  • 2,920
  • 5
  • 33
  • 64
  • In the Slowly Changing Dimension property window, dont you have the 'UpdateChangingAttributeHistory' property?. What stops you from changing it from false to true. May be i did not understand your issue. – Jithin Shaji Oct 29 '14 at 10:10
  • Indeed I can and do! But this doesn't appear to change the behavior of ssis. I may be misunderstanding what this property does, but I cannot see changes to the fact table after running it with this enabled. – Neil P Oct 29 '14 at 10:14
  • 1
    I always struggled with SSIS SCD task. Recently, I read like SCDs are worst in terms of performance. Now a days we use a set of LookUp task, OLEDB task to attain the SCD functionality. Just adding on, not the answer to your problem. – Jithin Shaji Oct 29 '14 at 10:16

1 Answers1

1

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.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25