September 11, 2011 at 7:44 am
Using SQL 2008 R2.
Tried to use a Lookup component to check if a record exists prior to loading into destination.
It is OK when matching to character and numeric columns, but tells me the data types don't match when I try to use date data types in the lookup. I eventually gave up and coded it as a stored procedure.
In short, does SSIS have problems with date data types in Lookup components?
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
September 26, 2011 at 10:42 am
I have the same problem. I have a DimDate dimension with an integer datatype primary key and a unique index date datatype as the business key / alternative key. Whenever I try to do a lookup in SSIS on the date field to get the primary key, SSIS throws an error saying that the data types don't match - even if I cast the lookup value as DT_DBDATE which is the date portion of a datetime with the time portion stripped away (for good discussion of the type, see: http://consultingblogs.emc.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-What_2700_s-the-difference-between-DT_5F00_DATE-and-DT_5F00_DBTIMESTAMP.aspx).
I'm using the Native OLE DB\SQL Server Native Client 10.0 for my connections, if that makes any difference.
A workaround for this is to create a view vDimDateLookup for the dimension with only the primary key and date fields, and convert the date to datetime in the view as:
Create View vDimDateLookup as
Select
DateKey,
Cast(Date as datetime) as Date
From DimDate
This works and is not too terribly inefficient since the date dimension has a relatively few number of records, but it's just wrong to have to do that!
September 26, 2011 at 12:55 pm
More info:
I was using a Derived Column transformation to cast a datetime value from my source database to DT_DBDATE and then lookup the value on a date datatype field in my DimDate dimension. This is were I was getting the error message saying that the data types didn't match.
The problem was that I was doing a replacement in the Derived Column transformation instead of add as new column. When you do a replacement on a column, SSIS does not allow you to change the data type of the column. So if I have a datetme data field SalesDate from production with the value of 01/01/2011 09:23:45 and do a replacement cast like (DT_DBDATE)SalesDate, I wind up with the value 01/01/2011 00:00:00 (the date portion) in the output column, but it's still a DT_DBTIMESTAMP data type and it will not match the type of the date field in my date dimension.
The correct solution is to add a new column, say SalesDateOnly, in the Derived Column as (DT_DBDATE)SalesDate. The type of this new column will be DT_DBDATE and it will match the type of the date field in my date dimension.
Gee, I HATE it when Microsoft's right....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply