views:

459

answers:

1

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?

+2  A: 

If I understand you, you have a data mart with a time dimension, and you need to get the timeId that corresponds to a particular time.

If that's correct, then you want to use a Lookup component. For the reference table use something like SELECT timeId, timeStamp FROM TimeDimension, then look up against the input column that holds the timestamp. Use the timeId as the output column, and now each row in your data flow will have the timeId that corresponds to its time stamp.

John Saunders
Thank you for the response! I believe this is the right direction however I am having problems making it work. The timestamp records in my TimeDimension are midnight records (12/02/2008 12:00:00 AM) whereas the time records in my source data are exact records (10/26/2008 6:40:46 PM). How can I have the lookup function avoid the time? I assume a fuzzy lookup component but I am having issues making it work. Thanks!
Sangheili
When you create the query for the source component, have one column be the rounded timestamp: DATEADD(DAY, DATEDIFF(DAY, 0, timestamp), 0)
John Saunders
Im trying to use this in a Derived Column component and I am getting an error I cannot figure out. Im taking the source column and outputting to a new column with the following function - "DATEADD("DAY", DATEDIFF("DAY", 0, [createDateTime] ), 0)". The error im getting is as follow: "The function "DATEDIFF" does not support the data type "DT_I4" for parameter number 2. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator." It works fine in a query... O.o
Sangheili
Sorry I wasn't clear. I meant for you to use that expression in the SELECT statement that produces the input rows. I suppose if you just use a table, then you'd be stuck with the derived column. It looks like DATEADD("DD",DATEDIFF("DD",(DT_DATE)"1/1/1900",timestamp),(DT_DATE)"1/1/1900") would work.
John Saunders
That worked! You rock :-) Can I pick your brain about how I could do the same process for the time only and not the date? I have a DimTime table with a record for every second in the day. It has a column with the standard time (ex: 12:03:23 AM) so if i could strip the date portion off the datetime stamp I could do a similar lookup on the time value and find the id for the time record.
Sangheili
Do the same thing, but substitute seconds instead of "DAY". This works for any date granularity supported by DATEADD, DATEPART, DATEDIFF, etc.
John Saunders