The Period Table task that comes with the ETL Studio creates Years, Months, Dates etc.
It does not, however, create hours, minutes, seconds.
In this example I will show you how you can create a new Period table, including hours, from the existing Period table.
My ETL project looks like this:
The Bulk Import task, the Period Table task and the Save task are all standard tasks that I have added without modifications.
The code for the Create HoursTable with 1-24 script:
IF [HoursTable].exists=true THEN
CREATE table [HoursTable] (Hours INT)
DECLARE @counter AS INT
SET @counter = 1
WHILE @counter <= 24
INSERT INTO [HoursTable] (Hours) VALUES (@counter)
SET @counter = @counter + 1
The code for the PeriodTableHours import task:
SELECT * FROM [PeriodTable] CROSS JOIN [HoursTable]
The cross join will result in a new table like this - where hours are being repeated 1-24 for every date: