Suppressing nodes in BizTalk mapper part deux

I wrote some post back about how to suppress nodes in BizTalk, using the graphical interface. This solution was recently under review and we had to implement some further changes to it. First I thought this could be solved in the GUI, having spent some time in there trying to fix it, I had to give up, this could not be solved using functiods or the likes. That is when I (for the second time) discovered the power of XSLT. XSLT or Extensible Stylesheet Language Transformations, is the way to go, very few limitations exist and at the end of the day, you end up with a mapping which is easier to understand and gives you a good overview, because, let’s face it, the graphical mapping in BT is anything but easy overview.

Calendar table in SQL

Found myself needing a calendar table to join my data to, in order to ensure that each day of the week and each day of the month had one Point in the graph, regardless of any sales was made that day. This below code does the trick and is quite straightforward. Note this is with the European week numbering, where the first day in the week is Monday.

Create the table
CREATE TABLE [dbo].[MyCalendar](

[DayNumber] [int] NULL,
[CalendarYear] [int] NULL,
[CalendarMonth] [int] NULL,
[DayInMonth] [int] NULL,
[WeekDay] [varchar](10) NULL,
[IsWeekEnd] [smallint] NULL,
[Period] [varchar](7) NULL,
[CalendarDate] [datetime] NULL,
[DateString] [varchar](10) NULL

) ON [PRIMARY]

This scrip will populate the tabel with all dates from @FromDate until @ToDate


declare @StartDate datetime
declare @EndDate datetime
declare @_date datetime
declare @counter int

set @StartDate = '2010-01-01'
set @EndDate = '2020-12-31'
set @counter = 0
set @_date = DateAdd(dd, @counter, @StartDate)

while @_date < @EndDate

begin

insert into GAB_Calendar values(
@counter, YEAR(@_date), MONTH(@_date), DAY(@_date)
,case when DATEPART(DW, @_date) = 1 then 7
when DATEPART(DW, @_date) = 2 then 1
when DATEPART(DW, @_date) = 3 then 2
when DATEPART(DW, @_date) = 4 then 3
when DATEPART(DW, @_date) = 5 then 4
when DATEPART(DW, @_date) = 6 then 5
when DATEPART(DW, @_date) = 7 then 6 end
,case when DATEPART(DW,@_date) in(1,7) then 1 else 0 end
,CONVERT(varchar, YEAR(@_date)) + '-' + CONVERT(varchar, MONTH(@_date))
,@_date
,CONVERT(varchar(10), @_date, 120)
)
set @counter = @counter + 1
set @_date = DateAdd(dd, @counter, @StartDate)
 end