Lookup Transformation fails on DT_STR (3) string match

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

Lookup Transform with Variable Parameter

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

Brandon I don't believe this can be done with the Lookup Transform, as I have ran into this limitation before.
Adrian
|||

The way I did it was to use a derived column transform before the lookup transform that "transform" my variable in a column. By doing this, I can now see the new column in the input column of the "set query parametsrs" parameter window.

Ccote

Lookup bug (altering caching breaks joins)

There is a bug in the Lookup components, that changing the caching mode breaks the join logic.This is even documented!
AFAICT, if you want the full join logic (where it matches on null values as well), you simply must use full caching everywhere, and install more memory as needed. :(
Does anyone know if this will be fixed with the next version, or in the release?

This is not a bug, as it is by design. If you would like to see a change in functionality please file a design change request in betaplace (or let me know if you can not and I will file one on your behalf.

Thanks,|||"Enabling memory caching breaks joins" seems to me like an annoying bug that is likely to bite newbies, even if they call it a "feature".
I cannot get into betaplace (I went around in circles with it for a while, trying to click on invisible buttons, and posting to the Microsoft SSIS beta newsgroups asking for help, and I never got any success).
If you could post it as a bug (or, um, "feature" that is likely to cause grief to users), that would be great.

Lookup - full caching vs partial caching vs no caching

I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).

Full caching:
SSIS caches all million records (consuming around 1 GB RAM) and cripples my lowly system with only 1GB physical RAM. This works for now, but the lookup tables will keep on growing. This means that full pre-caching may not scale for this scenario.

Partial caching and No caching:
I don't really know how SSIS works in these modes. I can make my guesses but it will be great if someone can explain those. The behavior that I see when I use partial cache of varying sizes or when I use no cache at all is that SSIS loads one buffer size of rows (6040) from the source and processes them. Then it loads next 6040 rows from the source. Ofcourse, due to cache limit, processing slows down considerably. Problem with this approach is that the source can have anywhere from 300,000 records to 2 million records. Processing them at 6040 rows at a time at the reduced speed due to caching, takes a long long time. Ofcourse, the system doesn't cripple. But the duration to execute the same package gets magnified a lot (I have not collected statistics yet but it is close to order of 10 i.e. 5 mins with full pre-caching become 40-50 mins or maybe more).
Any suggestions? Ideas? Experience with partial cache?

thanks,
NiteshNitesh,

Partial and no cache are very similar. In no cache the lookup makes a round trip to the database for each lookup. For partial case the lookup sees if the value to lookup is in the case, if so then it uses it. If not then it makes a round trip to the database and adds that record to the cache. If the cache is full then LRU is used to remove an entry so this new one can be added.

So it seems you have several options to evaluate:

1. Order your data so that the lookup hits the cache a lot.
2. Create a temp lookup table that contains the most frequently hit lookups (if this is indeed the case in your data sets) and do a full cache on this, then use an error output from that lookup to go to a partial or no cache lookup for the ones that don't hit in the full cached frequent items lookup.
3. Use a MergeJoin as an alternative as MergeJoin can be used to do a lookup. Generally it is not as performant due to the lookup's caching but it may be if you are using partial or no cache due to memory constraints.

HTH,
Matt|||Of course you must bear in mind that partial and no cache options do not join correctly if there are any null values :(
(This is a bug that is designed in, and documented as so.)
|||I've found that by writing the sql to select the fileds you require rather than checking the boxes from the whole list of available fields for the table the lookup cahces a lot less data. It appears that even if you only select a couple of columns from say a 10 column table i.e 1 filed to be returned and one field for the join (selected from the right table) the lookup still pulls back the whole table rather than just the selected fields it requires.

Therefore if you write the select to pull back only the fields that your require, for example it could be a key field for the join and an email address to be returned by the lookup. Then when you go to the second tab you will only see these two fields in the table on the right side. You will find that your lookup runs a lot quicker and consumes a lot less memory|||How does one turn on "partial caching" for a lookup transformation?
|||

Set CacheType="Partial"

CacheType is a property of the LOOKUP transform.

-Jamie

|||Ah, yes. I was looking for it on the Edit dialog, not the property page. I sometimes forget to look in both places.

Thanks,
Dan

Lookup - full caching vs partial caching vs no caching

I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).

Full caching:
SSIS caches all million records (consuming around 1 GB RAM) and cripples my lowly system with only 1GB physical RAM. This works for now, but the lookup tables will keep on growing. This means that full pre-caching may not scale for this scenario.

Partial caching and No caching:
I don't really know how SSIS works in these modes. I can make my guesses but it will be great if someone can explain those. The behavior that I see when I use partial cache of varying sizes or when I use no cache at all is that SSIS loads one buffer size of rows (6040) from the source and processes them. Then it loads next 6040 rows from the source. Ofcourse, due to cache limit, processing slows down considerably. Problem with this approach is that the source can have anywhere from 300,000 records to 2 million records. Processing them at 6040 rows at a time at the reduced speed due to caching, takes a long long time. Ofcourse, the system doesn't cripple. But the duration to execute the same package gets magnified a lot (I have not collected statistics yet but it is close to order of 10 i.e. 5 mins with full pre-caching become 40-50 mins or maybe more).
Any suggestions? Ideas? Experience with partial cache?

thanks,
NiteshNitesh,

Partial and no cache are very similar. In no cache the lookup makes a round trip to the database for each lookup. For partial case the lookup sees if the value to lookup is in the case, if so then it uses it. If not then it makes a round trip to the database and adds that record to the cache. If the cache is full then LRU is used to remove an entry so this new one can be added.

So it seems you have several options to evaluate:

1. Order your data so that the lookup hits the cache a lot.
2. Create a temp lookup table that contains the most frequently hit lookups (if this is indeed the case in your data sets) and do a full cache on this, then use an error output from that lookup to go to a partial or no cache lookup for the ones that don't hit in the full cached frequent items lookup.
3. Use a MergeJoin as an alternative as MergeJoin can be used to do a lookup. Generally it is not as performant due to the lookup's caching but it may be if you are using partial or no cache due to memory constraints.

HTH,
Matt|||Of course you must bear in mind that partial and no cache options do not join correctly if there are any null values :(
(This is a bug that is designed in, and documented as so.)
|||I've found that by writing the sql to select the fileds you require rather than checking the boxes from the whole list of available fields for the table the lookup cahces a lot less data. It appears that even if you only select a couple of columns from say a 10 column table i.e 1 filed to be returned and one field for the join (selected from the right table) the lookup still pulls back the whole table rather than just the selected fields it requires.

Therefore if you write the select to pull back only the fields that your require, for example it could be a key field for the join and an email address to be returned by the lookup. Then when you go to the second tab you will only see these two fields in the table on the right side. You will find that your lookup runs a lot quicker and consumes a lot less memory|||How does one turn on "partial caching" for a lookup transformation?
|||

Set CacheType="Partial"

CacheType is a property of the LOOKUP transform.

-Jamie

|||Ah, yes. I was looking for it on the Edit dialog, not the property page. I sometimes forget to look in both places.

Thanks,
Dan