Lookup SQL Override

hi,

i trying understand advanced lookup functionality in ssis.

here actual scenario: dim_details (lookup table) ------------- first_name   | last_name   | middle_name   | addr1  |  state   | country | lno -----------   ----------     ------------    ------    ------   --------  ----- james           john          michael      abc st      ny        us       k1998ny23 mary            clarke        james        abc st      ny        us       k2010ny29 source excel file has values follows: excel file (source file) ---------------------- first_name   | last_name   | middle_name   | addr1  |  state   | country | -----------   ----------     ------------    ------    ------   -------- james           john          michael      abc st      ny        us j               john          m            abc st      ny        us james           j             m            abc st      ny        us james           john          m            abc st      ny        us

the query have written me fetch lno column lookup table each value coming source. can see same person "james michael john" has 4 variations in source , each record need lno column lookup table.

i have written following query in lookup transformation editor -> advanced section -> modify sql statement:

  select * from (select * from dim_details) [reftable]   where charindex(cast(? as varchar(255)),[reftable].[first_name],1) > 0   and  charindex(cast(? as varchar(255)),[reftable].[middle_name],1) > 0    and charindex(cast(? as varchar(255)),[reftable].[last_name],1) > 0    and [reftable].[street] = ?   and [reftable].[city] = ?    and [reftable].[state_country] = ?  

the above logic moving "no matching entries". however, executing sql manually on database replacing parameters gives me desired output. can please guide me going wrong here? also, there anyway can monitor parameters being passed lookup while debugging (with similar data viewers) package? thanks, joice

ok - tried using sample data , definitions provided... weren't consistent (city/state, etc), worked "close".

the problem trailing spaces (in tests).  whatever reason, charindex function doesn't data type ssis presenting it.  don't understand why, ssis sending int data type sql parameter.  baffling.

so that's why had cast parameters varchar.  however, doing that, somewhere along line parameters being space-padded.  (use sql profiler see this.)  changing advanced statement rtrim result of cast, function should work.

however, in general, i'm not ecstatic solution - not because works - because it's uncached solution.  think should try cascacing lookup in scenario - cached lookup without advanced stuff attempts find "exact" matches, route no match outputs more complex lookups may or may not cached.

---

talk me on

SQL Server  >  SQL Server Integration Services