none
Lookup fails; how to substitute a default surrogate key value?

    Dotaz

  • Hi All,

    We have a lookup table ("tblLkup") that uses two columns (Col1, Col2) for its PK.  Those two columns are in our activity table ("tblActivity") as well.  The problem is that not all the combos in tblActivity actually exist in tblLkup, so the SSIS 2012 lookup transform fails.

    How can I force these failed lookups to use the default lookup row PK?  Every tblActivity row must use a surrogate key from tblLkup.

    Thanks,
    Eric B.

    čtvrtek 14. června 2018 0:00

Odpovědi

  • I'm not 100% clear as to what you are asking.  It appears that you have two columns that make up the Primary key.  However the table tblLkup has a surrogate key, which is what you are trying to get for your data pipeline.  If the combination of columns in tblActivity do not match any of the keys in tblLkup, then you want a some default value, which is value matching one of the surrogate key values in tblLkup.

    My thought is to redirect the row when there is no match.  Then in a derived column transformation, you simply hard code the default surrogate key.  Then you union all the no match path with the match path.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    čtvrtek 14. června 2018 1:08

Všechny reakce

  • I'm not 100% clear as to what you are asking.  It appears that you have two columns that make up the Primary key.  However the table tblLkup has a surrogate key, which is what you are trying to get for your data pipeline.  If the combination of columns in tblActivity do not match any of the keys in tblLkup, then you want a some default value, which is value matching one of the surrogate key values in tblLkup.

    My thought is to redirect the row when there is no match.  Then in a derived column transformation, you simply hard code the default surrogate key.  Then you union all the no match path with the match path.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    čtvrtek 14. června 2018 1:08
  • Thanks, Russ, I believe that's going to work for me.  I don't want to hard-code the default surrogate key, I'll use another Lookup instead.  (Hopefully the Full Cache from the other Lookup will be used for this second Lookup.) Other that that, I like your suggestion to my very poorly-worded question.

    čtvrtek 14. června 2018 16:06
  • To elaborate a bit further on my solution:

    Before doing the lookup, I split the data flow based on whether or not one of the PK columns is 0.  (Zero is a valid column value that exists in the lookup table.)  Whenever the value is 0, the other PK column value will not successfully look up, so these are my problem rows.  Both sides of that split do a lookup to the same table, but one of them only matches on a single column: the column with the zero.  Then, as Russ suggested, I union all those rows and I'm back in business.  This was actually much simpler than I assumed it would be!  All I needed to add was a split, another lookup, and a union all.


    čtvrtek 14. června 2018 16:30