Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

Problem with Date query

Hi!

I'm trying to query an Informix linked server to import data into a SQL database.

The OLE DB Provider is ifxoledbc. I can do general queries such as

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t ')

but when I try to add the where clause

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006"')

I get the error:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate="04/12/2006"" for execution against OLE DB provider "Ifxoledbc" for linked server "INFLINKMON".

Does anyone know what I am doing wrong? I can do a where query on other fields but not on date.

Thanks for any input that you can give.

Does the query run on the native system, without executing it through the linked server ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Thanks for responding...

Yes, the query runs on the native system, unix, by typing the select query on the command line exactly the same as the select in single quotes SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006" .

Thanks Again for any input!