SSIS

Have 8 million record table that my lookup transform is using, problem seems
the entire table is loaded into cache before the task begins. This of course
brings my ssis server to it's knees.

Played with 'Enable mem restriction' on Advanced tab, this just limits the
rows brought into cache which causes more misses of course. How to solve?

Wanted to use lookup transform for the following:
- Pull records from oledb source
- Lookup in oledb destination first to see if record exists
- if record does not exist, send to destination as insert.

This has got to be common w/ etl (if exists update, else insert), just can't
find any documenation for this specific 'how-to'...

any help much appreciated

tia
chris

Have you try to use the merge task instead?

merge the source table with the lookup content using a left join.
then add a split task which produce 2 streams, 1 with rows found in the 2
queries and the second one with the rows existing only on the left query.
(test if a column coming from the lookup query is null)


"Chris" wrote:

Hello Chris,


With the Lookup transform you can choose

Full Cache - bring the reference table into memory - good if you have lots
of memory
Partial - Add rows to the cache as they are used - Good if using less memory
or the range of rows looked up could be less
None - Always query the Db every time - useful in low memory conditions.


If you are struggling loading the 8 million into memory

Why not use a merge join instead with an OUTER join?

I often use this if the lookup references a good few rows.


Allan

Please explain - I do not see the option to select full/partial/none re
cache. In lookup trans all there is is a 'Enable memory restriction' checkbox
on advanced tab.

- When unchecked, all rows added to cache and the lookup actually finds it's
data and passes values to destination
- When checked, no rows are found and only nulls are passed to destination.
- When checked, & 'Enable caching' checked w/ value in megabyes, partial
records are found in lookup, the lower the mb, the fewer the rows found.

I prefer to cache none and do a database lookup - all I need to lookup is
maybe 200 records. Do not mind a database hit for each.

Thanks for you help

"Allan Mitchell" wrote:

Conclusion: when Lookup trans caching set to 'None', it will NOT perform
direct database lookup.

Chris

"Allan Mitchell" wrote: