SSIS: process lookup key is not found

SSIS: process lookup key is not found

Post by Bob Yan » Wed, 04 Jul 2007 07:52:33


I am wondering how does everyone handle the records if the key is not
found in the lookup table? I am thinking two ways:
1. Distinct the raw data field first and insert to the lookup table if
the key is missing before running lookup;
2. Run importing first and send those error records to a file. Then,
insert those missing keys to the lookup table and run the importing
again.

However, I think all these two methods may take a lot
of time since a lot of process to go through, so I just wonder if you
have any better way, for example, run inserting new key to the lookup
tables if the key is not found while in the importing process.

Thank you!
 
 
 

SSIS: process lookup key is not found

Post by Allan Mitc » Wed, 04 Jul 2007 17:25:19

Hello Bob,

I guess only your business process will tell you.

It may be that your process considers this an error.

It may be that your process wants you to create those rows in the lookup
table which is common when looking up rows from a dimension table when doing
a fact table load (Inferred Members)

If you have a table of failures that you previously created due to a key
not being found then you could always union that into the stream the next
time you do the load so you attempt to reprocess them.


Make sense?




Allan Mitchell
http://www.yqcomputer.com/ | http://www.yqcomputer.com/ | http://www.yqcomputer.com/ |
http://www.yqcomputer.com/

 
 
 

SSIS: process lookup key is not found

Post by Bob Yan » Thu, 05 Jul 2007 09:39:36

Allan,

actually, we want to see as less errors as we can (more automation).
in the other words, we try to insert as much as we can. If the lookup
key is not found then insert it too.

the only thing I worry about is "time," it may take a long time to
distint each column and compare and then insert only un matching ones.

The flat raw data is like below table (of course way more columns). it
takes long time to distint each column (I have 7 millions records now)
and then check the lookup tables then insert the missing ones. this
may take long time.



item,custmer, address, vender
-------,-----------,------------,-----------
TV, custmer1, address1, vender1
DVD, custmer2, address1, vender1
TV, custmer1, address2, vender2

thank you!