Is there a way to execute T-SQL code inside a data stream in SSIS?

Question

Background

I have a measurement table that has one record for each day. Each record has a primary key, so sample data:

Dimension table
---------------

---------------------------------
| ID | DateTime |
---------------------------------
| 1083 | 10/04/2008 10:02:00 PM |
---------------------------------

What I'm trying to do is take a source column that has a datetime SQL value (for example 04/10/2008 10:02:00 PM) and get SSIS, which should be the primary key from the dimension table ( 1083in the above example). I am trying to set this to a data stream in my package and avoid using staging tables.

I would like to call the database function during my data stream so that my SSIS package will detect timeidfor writing datetime. I tried using DeriveColumn, but this does not seem to allow T-SQL; but only the functions built into ANSI SQL.

Question

Is there any other way to do this inside the data stream? Or do I need to use staging tables and use SQLTaskoutside the data stream to manage my data?

Answer 1

If I understand you, you have a data packet with a time dimension, and you need to get the timeId value corresponding to a specific time.

, Lookup. - SELECT timeId, timeStamp FROM TimeDimension, , . timeId , timeId, .