Background
I have a dimension table that has a single record for each day. Each record has a primary key so example data would be:
Dimension Table --------------- --------------------------------- | ID | DateTime | --------------------------------- | 1083 | 04/10/2008 10:02:00 PM | ---------------------------------
What I am trying to do is take my source data column which has a SQL datetime value (such as 04/10/2008 10:02:00 PM
) and have SSIS derive what the primary key from the dimension table should be (1083
in the above example). I am trying to fit this into the Data Flow within my package and avoid using staging tables.
I would like to call a database function during my dataflow to have my SSIS package discover the timeid
for a datetime
record. I have tried to use DeriveColumn
but that doesn't seem to allow the use of T-SQL; rather only functions that are built into ANSI SQL.
Question
Is there another way to do this inside the dataflow? Or will I need to use staging tables and use a SQLTask
outside of the dataflow to manipulate my data?