May 14, 2008 at 1:21 pm
Hi,
I get the following error when i configure the lookup transform in the data flow task "Input column has a data type that cannot be matched".
This is the query that i use to set the reference table dataset
select firstname, lastname, address, email from customers_dimension cd , cust_test ct where cd.address<>ct.address.
I basically want to try and find all those records that have the same firstname, lastname, email in the customer dimension table where the records do not match. Both the input fields and the lookup fields have the same data type [varchar()].
Does anyone have a better idea as to what the problem is?
Thankyou
May 14, 2008 at 1:50 pm
You can use a lookup for this if you want. Re-check your data types on your data flow and on your table. SSIS is very sensitive about data types - including lengths. An NVARCHAR cannot be joined to a VARCHAR.
This join will also be CASE SENSITIVE, so if you want it to not be, you will need to upper-case or lower-case both sides of the join. It will also not ignore training spaces (which an ANSI join in T-SQL will), so take care to trim data if it may have trailing spaces on one side.
Finally, depending on the data set size, a lookup may be slow. You could use a Merge Join component or there is a table differencing component at http://www.sqlbi.com that you can download and use for free. These two options would require sorted data sets. You can sort your data sets in T-SQL rather than using a sort component, but you have to go into the advanced editor on the source component and tell it that the data set is sorted. Also keep in mind that the sort component in SSIS will use a case-sensitive, accent-sensitive sort and you database will sort based on your collation settings.
Now I have to go get my carpal-tunnel checked...
May 14, 2008 at 2:09 pm
Does everything in the caching T-sql statement look fine(advanced tab)
select * from
(SELECT firstName
FROM Customers_Dimension) as refTable
where [refTable].[firstName] = ?
Thnks, ill recommend a specialist for the hand once we're done..!
May 14, 2008 at 8:15 pm
I found the problem, apparently you cannot do a lookup if the two fields have a varchar(max), i had to convert the type.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply