May 8, 2007 at 12:00 am
Hi All,
I have a table called TimeDim it contains feilds like
timeKey int Unchecked
timeValue datetime Unchecked
calendarYear smallint Unchecked
calendarQuarter tinyint Unchecked
calendarMonth tinyint Unchecked
calendarDay tinyint Unchecked
fiscalYear smallint Unchecked
fiscalQuarter tinyint Unchecked
i want to add data to this table like 20 years back to present date how can i add data to this table using SSIS
i want timedim package
Please anybody help on this
Kindest Regards,
Sarath Vellampalli
May 8, 2007 at 8:03 am
Below is how you could do it using TSQL(which of course could be put in to a SSIS package), note that you may need to change the start date and also how far forward it runs (currently 5 years from today). Basically however you look to do this, you're going to need a loop that will allow you to generate/create records. If you wanted to do this as a purely SSIS exercise, you could try either the looping container in the package flow and then do an insert using an outer variable from that container. Alternatively, you could use a scripting task i the dataflow to generate your records.
DECLARE
@dateVar DATETIME
SET
@dateVar = '1986-01-01'
WHILE
@dateVar < DATEADD(yy, 5, GETDATE())
BEGIN
SELECT
CAST(CONVERT(CHAR(8), @dateVar, 112) AS INT),
@dateVar
,
YEAR(@dateVar)
-- etc etc etc
SET @dateVar = DATEADD(dd, 1, @dateVar)
END
Steve.
May 8, 2007 at 8:21 pm
Don't know if this is of use but I picked something like this off the web a while back and modified it to suit my needs. It does work ! Hope it at least gives you some pointers.
**************
USE
[Jericho]
GO
/****** Object: StoredProcedure [dbo].[usp_Build_Time_Dimension] Script Date: 05/09/2006 12:14:24 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author: Terence Starkey>
-- Create date: < 1st November 2006>
-- Description: <Build a time dimension table for Jericho DW>
-- =============================================
ALTER
PROCEDURE [dbo].[usp_Build_Time_Dimension] @StartDate datetime, @EndDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
TRUNCATE
TABLE TimeDim
DECLARE
@IncrDate datetime, --increment startdate in first WHILE loop
@MinRow
integer, --min row number in TimeDim
@MaxRow
integer, --max row number in TimeDim
@RowIncrement
integer, --number of rows to commit at a time (can adjust based on transaction log size)
@RowStart
integer, --beginning row number for update statements in second WHILE loop
@RowEnd
integer --ending row number for update statements in second WHILE loop
SELECT
@IncrDate = @StartDate,
@RowIncrement
= 1000
--seed the identity column in TimeDim
WHILE
@IncrDate <= @EndDate
BEGIN
insert TimeDim (FullDateAlternateKey)
select null
select @IncrDate = @IncrDate + 1
END
--populate time fields, processsing the number of rows in @RowIncrement
select
@MinRow = min(TimeKey),
@MaxRow
= max(TimeKey)
from
TimeDim
select
@RowStart = @MinRow,
@RowEnd
= @RowIncrement
WHILE
@RowStart <= @MaxRow
BEGIN
--populate FullDateAlternateKey field
update TimeDim
set FullDateAlternateKey = (@StartDate + TimeKey) - 1
where TimeKey between @RowStart and @RowEnd
--populate Day, Week, Month and Calendar Year fields from FullDateAlternateKey
update TimeDim
set DayNumberOfWeek = datepart(dw,FullDateAlternateKey),
DayNameOfWeek
= Datename(weekday,FullDateAlternateKey),
DayNumberOfMonth
= datepart(dd,FullDateAlternateKey),
DayNumberOfYear
= datepart(dy,FullDateAlternateKey),
MonthName = Datename(month,FullDateAlternateKey),
MonthNumberOfYear
= datepart(mm,FullDateAlternateKey),
CalendarYear
= datepart(yyyy,FullDateAlternateKey),
WeekNumberOfYear
= datepart(wk,FullDateAlternateKey)
where TimeKey between @RowStart and @RowEnd
--populate fields based on prior fields and init cap Month & Weekday
update TimeDim
set CalendarSemester =
CASE
WHEN datepart(qq,FullDateAlternateKey) in (1,2) THEN 1
ELSE 2
END,
FiscalYear
=
CASE
WHEN datepart(qq,FullDateAlternateKey) in (1,2) THEN RIGHT(CalendarYear - 1, 2) + RIGHT(CalendarYear, 2)
ELSE RIGHT(CalendarYear, 2) + RIGHT(CalendarYear + 1, 2)
END,
FiscalSemester
=
CASE
WHEN datepart(qq,FullDateAlternateKey) in (1,2) THEN 2
ELSE 1
END
select @RowStart = @RowEnd + 1,
@RowEnd
= @RowEnd + @RowIncrement
END
--of WHILE loop
END
May 8, 2007 at 9:11 pm
The function on the link below is designed to load a date dimension table for any range of dates that you supply. It contains over 60 columns of date attributes.
It does not load the fiscal year and quarter, because their definition is something that can be different for each organization. If you need help loading fiscal year and quarter, you should post the rules that determine them in your company.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply