Database Reference
In-Depth Information
13.
when the task is complete, stop Visual studio's debugger and verify that the task ran
successfully or troubleshoot any errors and try again.
14.
open sQl server Management studio and verify that dimPublishers has data in it.
Configure the Fill DimDates Execute SQL Task
we should to fill up the dimdates table before we fill the dimTitles table. This is because of the foreign
key relationship between the two tables. This is not strictly necessary since we have dropped the foreign
key constraints in the database, but filling the tables in the same order as you would as if the foreign key
constraints were still there documents the logical progression of the ETl process.
locate the comment
-- 2d) Create values for DimDates as needed
in the
sQl code file and review the code beneath it. The code should look like listing 8-4.
1.
Listing 8-4.
Code That Fills the DimDates Table
-- 2d) Create values for DimDates as needed.
-- Create variables to hold the start and end date
Declare @StartDate datetime='01/01/1990'
Declare @EndDate datetime='01/01/1995'
-- Use a while loop to add dates to the table
Declare @DateInProcess datetime
Set @DateInProcess=@StartDate
While @DateInProcess<= @EndDate
Begin
-- Add a row into the date dimension table for this date
Insert Into DimDates
( [Date], [DateName], [Month], [MonthName], [Quarter], [QuarterName], [Year],
[YearName] )
Values (
@DateInProcess -- [Date]
, DateName( weekday, @DateInProcess ) -- [DateName]
, Month( @DateInProcess ) -- [Month]
, DateName( month, @DateInProcess ) -- [MonthName]
, DateName( quarter, @DateInProcess ) -- [Quarter]
, 'Q'+DateName( quarter, @DateInProcess )+' - '+Cast( Year(@DateInProcess) as
nVarchar(50) ) -- [QuarterName]
, Year( @DateInProcess )
, Cast( Year(@DateInProcess ) as nVarchar(50) ) -- [YearName]
)
-- Add a day and loop again
Set @DateInProcess=DateAdd(d, 1, @DateInProcess)
End
2.
Highlight this code, right-click it, and choose Copy from the context menu.
3.
navigate to the Control Flow tab and select the Fill dimdates Execute sQl Task.
4.
Right-click this task and select Edit from the context menu.
