Hello,
My name is Sean, I’m dutch and a newbie in SQL Server Business Intelligence Development Studio.
I learned myself SQL Server 2008 BIDS by buying a book and reading articles on the internet.
So far, I managed to set up several ETL packages, but now I’m facing a problem with a package called DDS dim_product to load a dimension table, called dim_product, with the columns product_key, product_code, name, start_date_product, end_date_product, date_price, price, effective_timestamp (datetime), expiry_timestamp (datetime )and is_current (tinyint).
I created two Execute SQL Tasks, called Set CET (Current Extration Time) and Get LSET (Last Successfull Extration Time). In the Get LSET I assigned the values to two variables, called User::dtCET_Product and User::dtLSET_Product
Then I created a Data Flow Task, called DDS dim_product. On the Data Flow tab there s an OLE DB Source called NDS product, a Lookup called name and a Slowly Changing Dimension called DDS dim_product. In this SCD, the product_key is the Business key. date_price and price are Historical attributes, the other columns are Changing. I left the Changing attributes checkbox blank. Use start and end dates to identify current and expred records is selected, the Start date column is effective_timestamp, the End date column expiry_timestamp and Variable to set date values is User::dtCET_Product. Enable nferred member support is checked, as well as A columns with a change type are null.
Finally I created an Execute SQL Task, called Update LSET.
The table product in the orginal database has two rows both with a date_price, let say January 1 2010.
After executing the several packages I created, all the boxes turn green and there are two rows in the dim_product table both with effective_ timestamp set to 2010-01-01.
Then the date_price of one of the products changes to, let say July 1 2010. Again I executed all the packages, but when I check the table dim_product in SQL Server Management Studio I notice that there are three rows now, which of course is correct, but the column expiry_timestamp of the row (that changed) with price_date 2010-01-01 is still ‘Null’. I expected there should be the date 2010-07-01 in this column and I can’t figure out what’s the reason for this.
Who can explain me why there is no date in this column?
The SCD task called DDS dim_product automatically creates a Derived Column task to Set old row's expiry date with the Expression (DT_DBTIMESTAMP)(@[User::dtCET_Product]), an OLE DB Command to Expire the old row with the SQL Command UPDATE [dbo].[dim_product] SET [expiry_timestamp] = ? WHERE [product_key] = ? AND [expiry_timestamp] IS NULL, a Union all task, another Derived Column task to Set new row's effectve date with the Expression (DT_DBTIMESTAMP)(@[User::dtCET_Product]) and an OLE DB Destination task to insert into DDS dim_product.