October 2, 2009 at 9:27 am
Hi,
Who can help me. I tried to google it, but without any results.
I am a SSIS 2008 developer. I have to lookup dimension keys for a given fact table record ID, in my case IntransitCode.
My IntransitCodes dimension contains only 2 records. 0 = Not Intransit, X = Intransit
In my Stage table I have 389 records. 388 with IntransitCode 0 and 1 record with IntransitCode X
By accident I found out that dimension keys are lookupped wrong for my IntransitCodes dimension.
I use a lookup component in Full cache mode with a SQL Connection Manager. The SQL for the connection manager is: SELECT IntransitKey, IntransitCode FROM IntransitCodes
In the Columns tab I link IntransitCode of my stage table to the IntransitCode of the SQL query of the Connection manager. I then return my IntransitKey to the dataflow.
Now the problem: I expect to have 388 times the key for IntransitCode 0 and 1 time the key for IntransitCode X. Strange enough, I have for all records the dimension key for IntransitCode 0, which is off course not correct.
I 've read about Case Sensitivity in a Lookup component: you have to put the lookup in No Cache mode then to force not case sensitivty. But this doesn't apply to my situation. In the stage table and the dimension table, both the IntransitCode is a capital X.
Anybody any advice?
Are the keys returned by my other lookup components, based on the same technology as above, trustfull?
Is it worth to recreate the lookup component to see if the weird error disappears?
Regards,
Dennis
October 5, 2009 at 4:46 am
Do you use a upper and/or trim before you go to the Lookup transformation?
October 5, 2009 at 8:28 am
I'd look into Space Sensitivity as noted by Tim Mitchell in this blog post.
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 5, 2009 at 10:24 am
I have experienced this as well, I was often getting no matches when I could see the match in the table.. It wasn't till I forced a trim in the source query that I got very few no matches.
As a side not, I have had VERY good luck with the lookup, it works pretty good.
CEWII
October 5, 2009 at 10:40 am
Elliott W (10/5/2009)
I have experienced this as well, I was often getting no matches when I could see the match in the table.. It wasn't till I forced a trim in the source query that I got very few no matches.As a side not, I have had VERY good luck with the lookup, it works pretty good.
CEWII
I have had good success with the lookup as well. Once I learned about the different issues with sensitivity.
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 6, 2009 at 8:13 am
Hi all,
Thanks for your postings.
Before starting my post: I read this morning a cartoon that eating too much candy can make you aggresive. 😛
To be aggresive can lead to good results!
Every data transfer I start always with a TRIM, so trailing spaces is not the case.
I understand the Case Sensitivity of the LookUp Component. I 'll prefer the Full Cache Mode, to prevent a record by record query to the SQL server. So the LOWER() or UPPER() function for the Lookup Query as well as the input Dataflow is necessary. (I need sometimes Partial Cache mode for Type 2 dimensions, which lookup keys in a specific date range of the fact).
But trying your suggestions on my stage table still didn't give me the correct key.
After eating another candy, and looking close to my Data Viewers, I found something strange:
I had two columns with the same name in the Data Viewer and off course the wrong column was linked in the Lookup component. This is because I copied the data from our Inventory SSIS package. For Inventory, IntransitCode should always be "0". This was hard copied in one of the Derived Column components, which I copied 1 by 1 to my InTransit SSIS package. :angry:
I have corrected it by deleting the standard IntransitCode ="0" field in one Derived Columns and modifying the package fields, and here we go..... It worked. It also worked without the UPPER() function in the lookup query, thanks to the good data requirements of our database. 😉
Strange enough BIDS didn't give me any warning of having two columns with the same name in the data.
So in the future I will eat a lot of candy :w00t:
Thanks for your suggestions. If I can ever be of your assistance, let me know.
Best regards,
Dennis
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply