0

I am stuck with the following problem on MS SQL Server 2012 with VS 2010:

I want to use the SSIS for Slowly Changing Dimensions for changing a historical attribute. As the wizard only gives me the opportunity to decide to either save the information, whether the record is outdated or not, in one column or in the two date columns (see https://msdn.microsoft.com/en-us/library/ms187958.aspx). But I want to do both, save the information in the column 'Status' and also update 'StartDate' and 'EndDate'.

Example for what a table of employees should look like after using SSIS:

Employee Emma Johnson gets married and thus, on 20-02-2013, changes her lastname to Smith.

EmployeeID | EmployeeIDAlternateKey | Firstname | Lastname | StartDate | EndDate | Status

1 | 123 | Emma | Johnson | 01-01-2013 | 20-02-2013 | NULL

2 | 123 | Emma | Smith | 20-02-2013 | NULL | Current

Thanks for your help!

DEls
  • 241
  • 3
  • 14
  • "I want to use the SSIS for Slowly Changing Dimensions" - my advice: Don't!! See book "Expert Cube development" – Mitch Wheat Jan 29 '15 at 08:56

1 Answers1

0

What about you implement that logic of yours in the split that the slow changing dimensions component generates?

if the record is to be inserted set the status to "current"; and insert the record in the dimension

if the record is to be updated set the status to "NULL"; and update the record in the dimension

SQL.injection
  • 2,607
  • 5
  • 20
  • 37
  • Thanks, I managed it through updating the query in the 'OLE DB Command' for the 'Historical Attribute Inserts Output'-Path:UPDATE [dbo].[DimEmployee] SET [EndDate] = ?, [Status] = NULL WHERE [EmployeeNationalIDAlternateKey] = ? AND [EndDate] IS NULL – DEls Jan 29 '15 at 12:32