Hi, i have created an SSIS package using Slowly Changing Dimension Transformation. i have IsActive (Y/N) Flag in Source and Destination table. now when i execute my package it works fine. i am using Type 2 SCD so it adds new record. Now, for every new record it would have Y in IsActive Flag but for its old record it should be set to N.
Can somebody tell me how do i update my SSIS package so it would update the record to N if its changed?
asked Jan 20 '12 at 12:46 PM in Default
You should have a Historical Attribute Inserts Output connected to an OLE DB Update Command where you UPDATE the IsActive to No for IsActiveYes and where the business key is equal to business key coming through this output. Then you can union this output with New Output which contains all new records and insert those to the table with flag IsActive = Yes.
The Historical Attribute Inserts Output contains records which are updates to existing one for the SCD type 2. So as mentioned above you update current records of incoming business key to Non Current and then insert the new data with Current Flag.
By default the SCD wizzard takes care about the scenario I have described above. If you have chosen a single field for detection of the current record, then it is exactly the scenario described. If you have validity dates fields and chosen those in the wizard, then the solution is the same. If you want to use both validity range and Current Record flag, then simply use one or other method in SCD wizard and simply update the command n the OLE DB Command on the Historical Attribute Inserts Output Path to set the IsCurrent to False or the ValidTo date to the current date for ending the record validity.
answered Jan 20 '12 at 01:56 PM