The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.
This appears to be a bug to me.
By chance is it comparing against a CHAR field as opposed to a VARCHAR field?|||Yes, the source field was a varcahr(3) and the lookup field was a char(3). However, I modified the lookup field to be a varchar(3) with the same results. One note: this field is currently storing a two-digit value (ISO Country code, e.g. US). That is what originally led me to change it to a varchar(3) field originally. I have trimmed and converted until I have run out of options.|||Phil,
What is the optimal data type set up for matching strings, i.e char or varchar, trim both prior, etc?
|||I think the behaivior you guys described is alrady documented:
from http://msdn2.microsoft.com/en-us/library/ms141821.aspx
Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to use full precaching, Integration Services does the lookup comparison in the cache; otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation may return a different number of matches from the same lookup table depending on the cache type.`
And from connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126618
Rafael Salas
No comments:
Post a Comment