This usually happen if you need to insert Date Key into a fact table. You need do convert a date from the source table which has datetime data type to integer, i.e. from ‘2012-05-26 00:00:00’ to 20120526.
In T-SQL you can do this to convert datetime to int:
convert(int, replace( convert(varchar(10), @Date, 20) ,’-',”))
But in SSIS data flow, what do you do?
You need to use derived column like this:
The Derived Column transformation will add a new column to the workflow, which is the integer Date Key.
In SSIS we have the following date and string functions:
So to convert today’s date to integer, the idea is to get the year component, and the month component and the day component (as 3 different string), then concatenate them, then convert to integer.
So in the derived column we do these 3 components:
(DT_WSTR,4) YEAR(GETDATE()) ----- (1) (DT_WSTR,2) MONTH(GETDATE()) ------ (2) (DT_WSTR,2) DAY(GETDATE()) ------- (3)
But we need to remember that for November, the (2) output is 2 digit (11). But for May, the (2) output is 1 digit (5). So for 22nd May 2012 it would be 2012522 because May is single digit. So we need to pad it with zero like this:
RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 )
And the same with date: if the date is 1st to 9th, it will be a single digit. So 6th May 2012 will be 2012056, because 6th is a single digit. So we need to pad it with zero, like this:
RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )
And finally we combine those 3 components using “+” like this:
(DT_WSTR,4) YEAR(GETDATE()) + RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 ) + RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )
So we will get a string like this: “20120522″.
As the last step, we just need to convert it to integer, using (DT_I4), like this:
(DT_I4)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2))
In SSIS we have the following type cast operator available to us:
Here’s how it looks as an expression on the Derived Column window:
So once again the expression we need to convert today’s date (datetime) in Derived Column to integer is:
(DT_WSTR,4) YEAR(GETDATE()) + RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 ) + RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )
Hope this helps,
Vincent Rainardi 26/5/2012
[...] See the complete article here. [...]
Pingback by How to Populate a Fact Table using SSIS « Data Warehousing and Business Intelligence — 26 May 2012 @ 2:25 pm |
[...] part 1 (link) we have populated a fact table (FactHire) by reading the source table (Hire) and looking up 3 [...]
Pingback by How to Populate a Fact Table using SSIS (part 2) « Data Warehousing and Business Intelligence — 26 May 2012 @ 9:47 pm |
As a workaround, could the addition of a new field be done as part of the OLEDB Source task, thus minimizing the code for the derived column task.
SELECT CONVERT(INT,CONVERT(VARCHAR,GETDATE(),112)) AS DateKey
Comment by tarunrodrigues — 26 July 2012 @ 7:22 am |
Also using the convert function as part of the sql code, will eliminate the need to append 0 for numbers 1-9, to get it in YYYYMMDD format.
Comment by tarunrodrigues — 26 July 2012 @ 7:24 am |
Yes we can do the conversion on the SQL task Winston
Comment by Vincent Rainardi — 26 July 2012 @ 8:42 am |