CODEWITHSUNDEEP
sqlssissql-server-2008-r2business-intelligence
Pete Dragun
Pete Dragun

Reputation: 37

SSIS issue with historical data

I have a dimension of metrics found in a table called DimMetrics. The columns are as follows:

MetricSK - unique key
MetricAK - surrogate key
Source 
Status = Current, Expired
LastUpdate = date of the startDate of my range.

I am pulling data from one datasource to this one. Where I am retreiving storing the MetricAK and Source on a monthly basis.

The records could change from month to month, where the source could be deleted or added.

What is the best mehtod to achieve this? tried using the Slowly Moving Dimension provided, but I only managed to get it to work by adding records creating new MetricSK's.

What I would like is when i do a monthly import SSIS will check curren trecords and set records that are not part of the new batch to Expired, and then add any new records with Current and the first day of the date range I choose.

I hope this make sense, as I am stuck in a viable solution.

Thanks, Pete

Upvotes: 0

Views: 294

Answers (1)

TMNT2014
TMNT2014

Reputation: 2130

Ok.. this is kind of a hack since the Microsoft SCD does not have an inbuilt flow for delete. Before you start the data flow task that does the SCD on your table. Use an Execute SQL task that sets the status of all the rows to Expired. Now in the data flow task for the SCD component you would need to add the flow for unchanged rows and then a OLEDB Command that updates the status back to Current. Again this approach would be a performance bottle neck if you are talking about several millions of rows. But for a medium size table this should be fine.

Upvotes: 1

Related Questions