January 13, 2006 at 8:01 pm
Our source data has time specified as number of seconds since 1/1/1970. The conversion from this number to date is accomplished with the following SQL:
select dateadd(second, txTime, '01/01/1970') as 'date' from <source_table>
(I hate the use of 'date' for a column name; but it predates me... pun intended).
Because I have two time fields in the same record that must have rows in the time_dim table, it is repeated for the second field. Then prior to building the fact table there is a string of lookups that each add the appropriate foreign key.
The current SQL for the dates is:
select time_key_id, date as txTime, date as orgTxTime from time_dim
The problem is that I cannot map an input column to a reference column... it complains of incompatible column data types. If I say to use a table instead of a query it automatically maps one column - but still gives the error.
I have already done the data conversion and I'm pretty sure of what I am mapping; but I cannot seem to get it. Anyone (Kirk, Jamie) have any ideas? Sure looks like a bug to me; but I'm always willing to accept operator error if I can get the job done.
I have taken about five different approaches... including using a union and only processing one column - nothing I do lets me map either the original input column with the function on it, or a "conversion column" (i.e., copy of ___).
Sure would appreciate a hand here. Thanks!
January 16, 2006 at 8:00 am
This was removed by the editor as SPAM
January 16, 2006 at 2:27 pm
And as it turned out this one was operator error. The datatypes were inconsistent. I had to add the original field with "dateadd" statement in the SQL as a new field so that it was in my data flow to use for the lookup of the time_dim record.
data flows are not at all easy to edit, and the package/solution got so locked up that Visual Studio couldn't even be stopped from task manager until the fourth try...
Granted, the mistake was mine; but the tool is so hard to use that it is rapidly becoming something to avoid. and all the help I got here... well, I'm quite sure that MS would like me to just go away. They refused to respond to a list of problems we've had with their tool and told me to post my issues/problems here, in this forum, and they will be addressed one at the time - yet they don't get addressed. Occasionally there is a defense of their product; but very little help.
When a product does the job it is supposed to do, and does it well, it becomes a commodity and competitive products are also produced by others. When the product doesn't work well; but does a half-reasonable job, the vendor has a captive market and can make feeble attempts to help the user - and given enough time, eventually create a good product.
It strikes me that we are being held captive... for now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply