Practical Business Intelligence

It's common practice to used meaningful integers as the surrogate key to your date dimension rather than meaningless identity numbers. So, the date December 5th 2008 would have the surrogate key 20081205. One of the advantages to this is that you don't need to look up to the dimension to get the date key when you are populating the fact table: you can just do:

select convert(int, convert(varchar, [MyDate], 112)) as [MyDateKey]

when you connect to the source.

Just occasionally, though, you might need to have date as an actual date for use in a Script - perhaps for doing date calculations, determining weekend events or similar. You could keep a copy of the column in the date data type inside your data flow but that's wasting valuable memory. So, you can convert it directly in a script task as follows:

Dim MyDateKey as Integer = Row.MyDateKey
Dim MyDate = New Date(Left(MyDateKey, 4), Mid(MyDateKey, 5, 2), Right(MyDateKey, 2))