February 15, 2008 at 2:49 pm
I am importing data from FoxPro free tables into SQL server and throught the process have several Lookup transformations, but I have one that I cannot get to find a match when I know they match! The FoxPro system has a marriage table that has marriage (a 1 character code, and desc (MARRIED, SINGLE, etc... char(10)). I am using this table to load a SQL Server table with an integer identity field for the "code" (marital_status_id) and the desc (marital_status varchar(10)) value. Then when I load people I am using a lookup transformation matching the FoxPro desc (trimmed) to SQL marital_status. Both values evaluate to the DT_STR type and since I am loading my SQL table from the FoxPro table case is the same as well.
Any ideas why I never get a match on 6000+ records?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2008 at 9:17 am
I changed to a Fuzzy Lookup with a minimum similariaty of 1 and a similarity threshold of .99 ( as high as it will go) and I get a match on every one. Does not make sense to me. I am still looking for someone with an explanation. I am willing to send a subset of dummy data for you to test on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2008 at 11:39 am
Okay, I found out that SSIS Lookups in Full Cache Mode are case-sensitive from these two places:
http://geekswithblogs.net/dtotzke/archive/2007/02/06/105585.aspx
I was able to solve my problem by going to the advanced tab and Checking Enable Memeory Restriction and Modify SQL Statement. Mapped the parameter and I was fine. It may still be hitting the DB once per lookup, but at least the lookup is working. I also used Upper() in both my Lookup Query and Source Query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2008 at 2:20 pm
Thanks! I've been racking my brain for the past couple of hours trying to figure out how I was getting more matches using T-SQL and an = than I was using fuzzy lookup in SSIS.
I couldn't find where you change the caching on the fuzzy lookup task so I went with the UPPER solution.
Worked great!
October 7, 2009 at 12:53 pm
Hi Jack Corbett,
it is very interesting to know that you are tyring to import data from a Foxpro DB to Sql server an dtrying to match all Records to import.
Also have you used caching on Fuzzy lookup? How did you use it?
can you send me the sample package? My email address is <rachuri73@gmail.com>.
Thanks in advance!
Ram
October 7, 2009 at 2:16 pm
Once I learned about Case Sensitivity in the Lookup Transformation I did not use the Fuzzy Lookup anymore. I don't know if there is caching in the Fuzzy Lookup Transform.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply