I'm using the SSIS Dimension Merge SCD Component (http://dimensionmergescd.codeplex.com/) and have a situation where I have a configuration with both SCD1 and SCD 2 columns. I have rows where the InferredMember flag is set however the component inserted new rows and did not reset the current flag on the existing inferred rows.
Does anyone else use this component and have you seen it work correctly? Am I misunderstanding? My understanding is the SCD2 columns become SCD1 where InferredMember is true, is this wrong?
Sorting is done in the database on the business key and the sort columns are set to match. The output of the DMSCD component are hooked directly to the OLE DB Command/OLE DB Destination components. This is in production and has otherwise been working correctly every day for months.
This is the Audit output from a run:
ExistingDimensionInputRowCount = 719941
SpecialMemberInputRowCount = 1
SourceSystemInputRowCount = 720516
UnchangedOutputRowCount = 719941
NewOutputRowCount = 720517
DeletedOutputRowCount = 0
SCD2ExpiredOutputRowCount = 0
SCD2NewOutputRowCount = 0
SCD1UpdatedOutputRowCount = 0
InvalidInputOutputRowCount = 0
So, to answer my own question, yes, I am mistaken. The InferredMember flag alone does not trigger Inferred Member behavior.
Inferred members are skeletal records inserted in the dimension tables - often by a stored proc. - when a surrogate key look-up fails during fact table maintenance. The InferredMember flag typically triggers the dimension load process to fill in the remaining fields on the skeletal Inferred Member records. And in the case of SCD2 fields on Inferred Member records they are handled as SCD1 and new records should not be generated.
Through experimentation I was able to determine that the DMSCD component requires the Inferred Member skeleton to include at least the Business Key, Inferred Member flag, and an Active Date that is in the past - I was using the current date so the records were not treated as Inferred Member records, they were being treated as New records and duplicates were being generated.
Regrettably, I have never been able to find documentation that calls out the specification for inferred member records that are compatible with DMSCD so that Inferred Member records can be properly formed by the stored proc that is developed to create them. I'd also question the inability to tag inferred member records with an active date that is the current date.