SCD object in SSIS

View previous topic View next topic Go down

SCD object in SSIS

Post  goldwhispers on Thu Apr 04, 2013 7:29 am

Is it possible to load history through the SSIS SCD object? I dont know if i am doing this incorrectly but when i load history, as far as i understand it shouldn't load duplicates yes after using this object i have the same value in the attribute with a new record? I have set it to keep history for a certain attribute. I have skuno, which is my businesskey (natural key), and then my historical attribute is my standard cost, however sometimes the standard cost doesn't change but there is a new record in the source table at a different date with the same standard cost value, surely it should only pick up if it has changed, however if you load it all in the same load will it not pick that up. How can i load this data accurately?

goldwhispers

Posts : 1
Join date : 2013-04-04

View user profile

Back to top Go down

Re: SCD object in SSIS

Post  Mike Honey on Thu Apr 04, 2013 6:59 pm

You need to manage your data such that you only pass the SCD component one row for each business key per Data Flow execution. The two main options are:
- pre-filter the data to drop all rows for the same business key except the "latest"
- wrap a loop around your data flow and only pass one "version" of your business key per loop

The SSIS SCD component does lots of things for you and saves a ton of code. I still persevere with it for those reasons. However it has several shortcomings and this is one. Once you understand it and work around it the component is reliable.

To be fair it's not it's fault - it processes the records row by row. The issue is that the downstream components it generates (e.g. OLE DB Destination) work on buffers of multiple rows. So your 2nd row can arrive at the SCD before the 1st row has been inserted by the downstream OLE DB Destination.
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum