I have a simple lookup table with Code (PK, INT) and a Description (VARCHAR(100)) fields. There is a Unique Index on [Description]. There is an entry with [Description] = 'Other'. If I try to INSERT a value of 'OTHER' it will fail because of the violation
of the UNIQUE Constraint. If I SELECT from this table WHERE [Description] = 'OTHER' I will get the one row with 'Other'. All is well and good, this is as it should be.
I then reference that table in a Lookup Transform in SSIS, to 'lookup' the [Code] for any given [Description] coming down the data flow pipeline. When it encounters a value of 'OTHER' it does NOT make a match to the row with 'Other'.
Seems the collation rules of SSIS are NOT respecting that of the SQL Server.
Yes, I could always do a UPPER on both sides so as to switch everything to upper case, but I shouldn't HAVE TO. Is there anything else I could do to make SSIS think the 'Other' and 'OTHER' are equal, and therefore should be matched?
Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
View Complete Post