Generating Time Dimension with SSIS

Hi Everyone,

Time dimension is the most popular and common in data warehouse systems.

We always create scripts to generate time intelligence in our data warehouse and analysis services.

The SSIS package has two parameters (Variables) , "StartDate" , "EndDate".

There is a SQL script in the project to create the destination table (in TempDB).

Script component is the source of data flow and its generating a standard time dimension.

It also prepares "TimeKey" with the most popular expression, For Example:

Dim oStartDate As Date = CDate(Me.Variables.StartDate.ToString())
Dim oEndDate As Date = CDate(Me.Variables.EndDate.ToString())

        Do While oStartDate <= oEndDate
            With Me.MyAddressOutputBufferBufferBuffer
                .AddRow()
                .TimeKey = (oStartDate.Year * 10000) + (oStartDate.Month * 100) + oStartDate.Day
                .Day = oStartDate.Day.ToString()
                .DayName = DateAndTime.DatePart(DateInterval.Day, oStartDate, FirstDayOfWeek.Sunday).ToString()
                .Month = oStartDate.Month.ToString()
                .MonthName = MonthName(oStartDate.Month)
                .Quarter = DateAndTime.DatePart(DateInterval.Quarter, oStartDate, FirstDayOfWeek.Sunday).ToString()
                .QuarterName = "Q" & DateAndTime.DatePart(DateInterval.Quarter, oStartDate, FirstDayOfWeek.Sunday).ToString()
                .QuarterFullName = "Q" & DateAndTime.DatePart(DateInterval.Quarter, oStartDate, FirstDayOfWeek.Sunday).ToString() & _
                "/" & oStartDate.Year.ToString()
                .Year = oStartDate.Year.ToString()
            End With
            oStartDate = DateAndTime.DateAdd(DateInterval.Day, 1, oStartDate)
        Loop