Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Calendar Transform Sample

I'm currently sat, bored, in a hotel room and figured now would be a good time to address my chronic lack of blogging lately. In my defence I have been on holiday!

Anyway, I've been reviewing the sample CalenderTransform componentas provided by Microsoft. More accurately, it was provided by a very smart guy by the name of Grant Dickinson.

Here Grant has provided a transformation component that takes an incoming DT_DATE or DT_DBTIMESTAMP column and from it creates a dataset of information about each incoming date value such as:
-DayOfWeek
-DayOfMonth
-DayOfYear
-WeekOfYear
-MonthOfYear
-Year
and a multitude of other attributes that you will doubtless never need. Moreover, it produces all this information in Roman, Fiscal, Manufacturing and Reporting calendars.

Grant has leveraged the Microsoft.AnalysisServices.TimeDimGenerator namespace in order to do this which means that the component is dependant on your having Analysis Services installed locally. That sounds like a limitation but that isn't really the case. The aim of the sample isn't to provide a working component but instead to educate the community as to how to "roll your own" components. To that end Grant has achieved superbly.

Some of the code in there is way too advanced for an amateur C# developer like myself to understand but the basic concepts of implementing the PipelineComponent interface are very well demonstrated. All the normal design-time methods:
ProvideComponentProperties()
ReinitialiseMetadata()
Validate()

are covered plus less-seen event capture methods such as:
SetComponentProperty()
SetOutputColumnProperty()
SetOutputColumnDataTypeProperties()
InsertOutputColumnAt()
InsertInput()
DeleteInput()
InsertOutput()
DeleteOutput()

I haven't seen many (any???) demos/samples where they are all covered so this is really useful stuff.

There's other things in there which I had never seen before:
1) Throwing ComException exceptions
2) Using Resources
3) Using regular expressions to parse property values

Personally, I would rather the component were a source component that just generated a dataset of dates between two supplied values rather than a transform but to be honest, that doesn't really matter. Its very easy to generate a set of dates (using a trusty script component) to be used as input to this CalendarTransform.
Alternatively you can easily port Grant's code into a source component of your own - he's done all the hard work for you!

This is the first sample component that I've looked at. I plan to look at the others soon - I just hope they're not all as complicated as this one!

Here's a screenshot of the Calendar Transfrom at work:

20060214scr.JPG

-Jamie

Published Tuesday, February 14, 2006 2:46 AM by jamie.thomson

Comments

 

Professional Association for SQL Server (PASS) SIG said:

February 14, 2006 8:06 PM
 

Joel Janke said:

You mentioned in your article Jamie that you'd rather have a data source that gave you all the relevant records between any 2 dates. Here's an old school table valued function I created in SQL Server 2000 (works in 2005 as well) years ago that does just that. You just need to write a simply select statement like this...

SELECT *
FROM [dbo].[fn_TimeDimension] ('01/01/2004','12/31/2005')

Here's the function definition...enjoy.

/****** Object: UserDefinedFunction [dbo].[fn_TimeDimension] Script Date: 02/17/2006 14:59:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_TimeDimension](@StartDate datetime, @EndDate datetime)
RETURNS @TimeDimension TABLE
(
------------------------------------
-- Day Level Items
------------------------------------
[Day_Key] integer NOT NULL,
[Day_Date] datetime NOT NULL,
[Day_ISO_Date] char(8) NOT NULL,
-- Day Level Week Descriptors
[Day_Of_Week] tinyint NOT NULL,
[Day_Of_Week_Name] varchar(9) NOT NULL,
[Day_Is_Weekday] bit NOT NULL,
[Day_Is_Weekend] bit NOT NULL,
-- Day Level Month Descriptors
[Day_Of_Month] tinyint NOT NULL,
[Day_Of_Month_Name] varchar(16) NOT NULL,
-- Day Level Quarter Descriptors
[Day_Of_Quarter] tinyint NOT NULL,
[Day_Of_Quarter_Name] varchar(16) NOT NULL,
-- Day Level Half Descriptors
[Day_Of_Half] tinyint NOT NULL,
[Day_Of_Half_Name] varchar(16) NOT NULL,
-- Day Level Year Descriptors
[Day_Of_Year] smallint NOT NULL,
[Day_Of_Year_Name] varchar(20) NOT NULL,

------------------------------------
-- Week Level Items
------------------------------------
[Week_Key] integer NOT NULL,
[Week_Name] varchar(7) NOT NULL,
[Week_Name_With_Year] varchar(13) NOT NULL,
[Week_Short_Name] char(4) NOT NULL,
[Week_Short_Name_With_Year] char(9) NOT NULL,
[Week_Number] tinyint NOT NULL,
[Week_Start_Date] datetime NOT NULL,
[Week_End_Date] datetime NOT NULL,

------------------------------------
-- Month Level Items
------------------------------------
[Month_Key] integer NOT NULL,
[Month_Name] varchar(9) NOT NULL,
[Month_Name_With_Year] varchar(15) NOT NULL,
[Month_Short_Name] varchar(3) NOT NULL,
[Month_Short_Name_With_Year] char(8) NOT NULL,
[Month_Number] tinyint NOT NULL,
[Month_Start_Date] datetime NOT NULL,
[Month_End_Date] datetime NOT NULL,

------------------------------------
-- Quarter Level Items
------------------------------------
[Quarter_Key] integer NOT NULL,
[Quarter_Name] char(9) NOT NULL,
[Quarter_Name_With_Year] char(15) NOT NULL,
[Quarter_Short_Name] char(2) NOT NULL,
[Quarter_Short_Name_With_Year] char(7) NOT NULL,
[Quarter_Number] tinyint NOT NULL,
[Quarter_Start_Date] datetime NOT NULL,
[Quarter_End_Date] datetime NOT NULL,

------------------------------------
-- Half Level Items
------------------------------------
[Half_Key] integer NOT NULL,
[Half_Name] char(6) NOT NULL,
[Half_Name_With_Year] char(12) NOT NULL,
[Half_Short_Name] char(2) NOT NULL,
[Half_Short_Name_With_Year] char(7) NOT NULL,
[Half_Number] tinyint NOT NULL,
[Half_Start_Date] datetime NOT NULL,
[Half_End_Date] datetime NOT NULL,

------------------------------------
-- Year Level Items
------------------------------------
[Year_Key] integer NOT NULL,
[Year_Name] char(4) NOT NULL,
[Year_Short_Name] char(2) NOT NULL,
[Year_Number] smallint NOT NULL,
[Year_Start_Date] datetime NOT NULL,
[Year_End_Date] datetime NOT NULL
)
AS
BEGIN

DECLARE @DayKey Int
DECLARE @DayDate DateTime
DECLARE @DayISODate char(8)
DECLARE @DayOfWeek tinyint
DECLARE @DayOfWeekName varchar(9)
DECLARE @DayIsWeekday bit
DECLARE @DayIsWeekend bit
DECLARE @DayOfMonth tinyint
DECLARE @DayOfMonthName varchar(16)
DECLARE @DayOfQuarter tinyint
DECLARE @DayOfQuarterName varchar(20)
DECLARE @DayOfHalf tinyint
DECLARE @DayOfHalfName varchar(20)
DECLARE @DayOfYear smallint
DECLARE @DayOfYearName varchar(20)

DECLARE @WeekKey Int
DECLARE @WeekName varchar(7)
DECLARE @WeekNameWithYear varchar(13)
DECLARE @WeekShortName char(4)
DECLARE @WeekShortNameWithYear char(9)
DECLARE @WeekNumber tinyint
DECLARE @WeekStartDate as datetime
DECLARE @WeekEndDate as datetime

DECLARE @MonthKey Int
DECLARE @MonthName varchar(9)
DECLARE @MonthNameWithYear varchar(15)
DECLARE @MonthShortName varchar(3)
DECLARE @MonthShortNameWithYear char(8)
DECLARE @MonthNumber tinyint
DECLARE @MonthStartDate as datetime
DECLARE @MonthEndDate as datetime

DECLARE @QuarterKey Int
DECLARE @QuarterName char(9)
DECLARE @QuarterNameWithYear char(15)
DECLARE @QuarterShortName char(2)
DECLARE @QuarterShortNameWithYear char(7)
DECLARE @QuarterNumber tinyint
DECLARE @QuarterStartDate as datetime
DECLARE @QuarterEndDate as datetime

DECLARE @HalfKey Int
DECLARE @HalfName char(6)
DECLARE @HalfNameWithYear char(12)
DECLARE @HalfShortName char(2)
DECLARE @HalfShortNameWithYear char(7)
DECLARE @HalfNumber tinyint
DECLARE @HalfStartDate as datetime
DECLARE @HalfEndDate as datetime

DECLARE @YearKey Int
DECLARE @YearName char(4)
DECLARE @YearShortName char(2)
DECLARE @YearNumber smallint
DECLARE @YearStartDate as datetime
DECLARE @YearEndDate as datetime

SET @DayDate = @StartDate

WHILE DateDiff(day,@DayDate,@EndDate) >= 0
BEGIN

SET @DayKey = DateDiff(day,@StartDate,@EndDate) - DateDiff(day,@DayDate,@EndDate) + 1
SET @DayISODate = convert(char(8),@DayDate,112)

SET @YearNumber = DATEPART(yy,@DayDate)
SET @YearKey = @YearNumber
SET @YearName = right('0000' + cast(@YearNumber as varchar(4)),4)
SET @YearShortName = right('0000' + cast(@YearNumber as varchar(4)),2)
SET @YearStartDate = cast(@YearName + '-01-01' as datetime)
SET @YearEndDate = cast(@YearName + '-12-31' as datetime)
SET @DayOfYear = DATEPART(dy, @DayDate)

SET @QuarterNumber = DATEPART(q, @DayDate)
SET @QuarterKey = cast(cast(@YearKey as char(4)) + right('00' + cast(@QuarterNumber as varchar(2)),2) as int)
SET @QuarterName = 'Quarter ' + cast(@QuarterNumber as char(1))
SET @QuarterNameWithYear = @QuarterName + ', ' + @YearName
SET @QuarterShortName = 'Q' + cast(@QuarterNumber as char(1))
SET @QuarterShortNameWithYear = @QuarterShortName + ' ' + @YearName
SET @QuarterStartDate =
CASE @QuarterNumber
WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
WHEN 2 THEN cast(@YearName + '-04-01' as datetime)
WHEN 3 THEN cast(@YearName + '-07-01' as datetime)
WHEN 4 THEN cast(@YearName + '-10-01' as datetime)
END
SET @QuarterEndDate = dateadd(day,-1,dateadd(q,1,@QuarterStartDate))
SET @DayOfQuarter = DateDiff(day,@QuarterStartDate, @DayDate) + 1
SET @DayOfQuarterName = 'Day ' + cast(DateDiff(day,@QuarterStartDate, @DayDate) + 1 as varchar(2)) + ' of Q' + cast(@QuarterNumber as char(1))

SET @HalfNumber = CASE WHEN DATEPART(q, @DayDate) <= 2 THEN 1 ELSE 2 END
SET @HalfKey = cast(cast(@YearKey as char(4)) + cast(@HalfNumber as char(1)) as int)
SET @HalfName = 'Half ' + cast(@HalfNumber as char(1))
SET @HalfNameWithYear = @HalfName + ', ' + @YearName
SET @HalfShortName = 'H' + cast(@HalfNumber as char(1))
SET @HalfShortNameWithYear = @HalfShortName + ' ' + @YearName
SET @HalfStartDate =
CASE @HalfNumber
WHEN 1 THEN cast(@YearName + '-01-01' as datetime)
WHEN 2 THEN cast(@YearName + '-07-01' as datetime)
END
SET @HalfEndDate =
CASE @HalfNumber
WHEN 1 THEN cast(@YearName + '-06-30' as datetime)
WHEN 2 THEN cast(@YearName + '-12-31' as datetime)
END
SET @DayOfHalf = DateDiff(day,@HalfStartDate, @DayDate) + 1
SET @DayOfHalfName = 'Day ' + cast(DateDiff(day,@HalfStartDate, @DayDate) + 1 as varchar(3)) + ' of H' + cast(@HalfNumber as char(1))

SET @MonthName = DATENAME(mm, @DayDate)
SET @MonthNameWithYear = @MonthName + ', ' + @YearName
SET @MonthShortName = DATENAME(m, @DayDate)
SET @MonthShortNameWithYear = @MonthShortName + ' ' + @YearName
SET @MonthNumber = DATEPART(m, @DayDate)
SET @MonthKey = cast(cast(@YearKey as char(4)) + right('00' + cast(@MonthNumber as varchar(2)),2) as int)
SET @MonthStartDate = cast(@YearName + '-'+@MonthShortName+'-01' as datetime)
SET @MonthEndDate = dateadd(day,-1,dateadd(m,1,@MonthStartDate))
SET @DayOfMonth = DATEPART(d, @DayDate)
SET @DayOfMonthName = datename(m,@DayDate) + ' ' +
cast(datepart(d,@DayDate) as varchar(2)) +
CASE left(right('00' + cast(datepart(d,@DayDate) as varchar(2)),2),1)
WHEN '1' THEN
'th'
ELSE
CASE right(right('00' + cast(datepart(d,@DayDate) as varchar(2)),2),1)
WHEN '1' THEN 'st'
WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd'
ELSE 'th'
END
END
SET @DayOfYearName = @DayOfMonthName + ', ' + @YearName

SET @WeekName = 'Week ' + datename(wk,@DayDate)
SET @WeekNameWithYear =@WeekName + ', ' + @YearName
SET @WeekShortName = 'WK'+right('00'+datename(wk,@DayDate),2)
SET @WeekShortNameWithYear = @WeekShortName + ' ' + @YearName
SET @WeekNumber = datepart(wk,@DayDate)
SET @WeekKey = cast(cast(@YearKey as char(4)) + right('00' + cast(@WeekNumber as varchar(2)),2) as int)

SET @WeekStartDate = dateadd(day,(datepart(dw,@DayDate)-1)*-1,@DayDate)
SET @WeekEndDate = dateadd(day,-1,dateadd(wk,1,@WeekStartDate))
SET @DayOfWeek = DATEPART(dw, @DayDate)
SET @DayOfWeekName = DATENAME(dw, @DayDate)
SET @DayIsWeekday = CASE @DayOfWeek WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END
SET @DayIsWeekend = CASE @DayOfWeek WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END

INSERT INTO @TimeDimension
(
[Day_Key],
[Day_Date],
[Day_ISO_Date],
[Day_Of_Week],
[Day_Of_Week_Name],
[Day_Is_Weekday],
[Day_Is_Weekend],
[Day_Of_Month],
[Day_Of_Month_Name],
[Day_Of_Quarter],
[Day_Of_Quarter_Name],
[Day_Of_Half],
[Day_Of_Half_Name],
[Day_Of_Year],
[Day_Of_Year_Name],

[Week_Key],
[Week_Name],
[Week_Name_With_Year],
[Week_Short_Name],
[Week_Short_Name_With_Year],
[Week_Number],
[Week_Start_Date],
[Week_End_Date],

[Month_Key],
[Month_Name],
[Month_Name_With_Year],
[Month_Short_Name],
[Month_Short_Name_With_Year],
[Month_Number],
[Month_Start_Date],
[Month_End_Date],

[Quarter_Key],
[Quarter_Name],
[Quarter_Name_With_Year],
[Quarter_Short_Name],
[Quarter_Short_Name_With_Year],
[Quarter_Number],
[Quarter_Start_Date],
[Quarter_End_Date],

[Half_Key],
[Half_Name],
[Half_Name_With_Year],
[Half_Short_Name],
[Half_Short_Name_With_Year],
[Half_Number],
[Half_Start_Date],
[Half_End_Date],

[Year_Key],
[Year_Name],
[Year_Short_Name],
[Year_Number],
[Year_Start_Date],
[Year_End_Date]
)
VALUES
(
@DayKey,
@DayDate,
@DayISODate,
@DayOfWeek,
@DayOfWeekName,
@DayIsWeekday,
@DayIsWeekend,
@DayOfMonth,
@DayOfMonthName,
@DayOfQuarter,
@DayOfQuarterName,
@DayOfHalf,
@DayOfHalfName,
@DayOfYear,
@DayOfYearName,

@WeekKey,
@WeekName,
@WeekNameWithYear,
@WeekShortName,
@WeekShortNameWithYear,
@WeekNumber,
@WeekStartDate,
@WeekEndDate,

@MonthKey,
@MonthName,
@MonthNameWithYear,
@MonthShortName,
@MonthShortNameWithYear,
@MonthNumber,
@MonthStartDate,
@MonthEndDate,

@QuarterKey,
@QuarterName,
@QuarterNameWithYear,
@QuarterShortName,
@QuarterShortNameWithYear,
@QuarterNumber,
@QuarterStartDate,
@QuarterEndDate,

@HalfKey,
@HalfName,
@HalfNameWithYear,
@HalfShortName,
@HalfShortNameWithYear,
@HalfNumber,
@HalfStartDate,
@HalfEndDate,

@YearKey,
@YearName,
@YearShortName,
@YearNumber,
@YearStartDate,
@YearEndDate
)

SET @DayDate = DATEADD(day, 1, @DayDate)
END
RETURN
END
February 17, 2006 10:13 PM
 

Katharine said:

fn_TimeDimention is very helpful....Thank  You
April 20, 2006 10:58 PM
 

Johnson Bruno said:

Till today i am trying to read it completely

June 1, 2007 10:42 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems