January 24, 2012 at 7:28 am
sqlsponge2 (1/24/2012)
This is a screenshot of the join I can run in SSMS that shows the record 700 which is in both tables and then the records 701 - 710 are the records only in staging that are to be inserted.
What is the query that you defined in the Lookup component?
Can you post it here?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2012 at 7:30 am
SELECT *
FROM PurchaseOrders_CA
January 24, 2012 at 7:44 am
It might be a long shot, but the lookup transformation is case sensitive.
Try adding upper() around the OrderGroupID column in both tables, so that they are both upper case.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2012 at 7:58 am
That did not work either. Thanks for all your help. I will keep pounding a way. May try a different flow and try and use queries instead of lookup.
January 24, 2012 at 11:40 am
What is the metadata type of the column with the GUID as it enters the lookup component? I've seen that end up funky before, and then it tries to do a varchar() compare after the fact. You might be best off bringing both UUIDs in as DT_STR 1252 (code page will matter) and then comparing that way.
No, I don't know why SSIS 2k5 lookups has personal problems with GUIDs, but I've seen it before.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 12:19 pm
It appears to be coming in through the OLE DB source as (string [DT_STR]) and comparing in the Lookup to (string [DT_STR]).
January 24, 2012 at 12:45 pm
sqlsponge2 (1/24/2012)
It appears to be coming in through the OLE DB source as (string [DT_STR]) and comparing in the Lookup to (string [DT_STR]).
Hm. 11 records that won't associate, they're both comparing as DT_STR, other records do associate, and they're matching in datatype going into the Lookup. Modifying case sensitivity didn't affect the results, which means there's something in the data-layer itself causing the issue. But what?
Are these 11 records consistently the only ones being a problem? Are they stored as UniqueIdentifier in the source table (trying to see if they got extra spaces/invalid characters)? Can these 11 records match to themselves via lookup (seeing if it's something particular to Lookup)? IE: OLEDB source the original records then lookup themselves against themselves. I know, funky thing to check, but it'll let us know if its just the component or the data is somehow different between the two sources.
I'm not convinced the data is stored as expected, or being read from the other source as expected. A different dash, trailing spaces, an O instead of a 0 (I know, there are no Os in hex)... something like that. With both sides being UPPER()'d and both being DT_STR datatype with the same code page, I'd start inspecting the data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2012 at 12:53 pm
The 11 recoreds is my test set. I manually changed 1 record to prove my update process and deleted 10 records to have those to insert. I have checked the Production table where everything is starting from and the OrderGroupID is GUID and that flows all the way through Staging to History.
I am not sure if I understand some of your troubleshooting steps as I am a newbie and not sure of some of the terminology.
January 24, 2012 at 2:03 pm
Evil Kraig F (1/24/2012)
sqlsponge2 (1/24/2012)
It appears to be coming in through the OLE DB source as (string [DT_STR]) and comparing in the Lookup to (string [DT_STR]).Hm. 11 records that won't associate, they're both comparing as DT_STR, other records do associate, and they're matching in datatype going into the Lookup. Modifying case sensitivity didn't affect the results, which means there's something in the data-layer itself causing the issue. But what?
Are these 11 records consistently the only ones being a problem? Are they stored as UniqueIdentifier in the source table (trying to see if they got extra spaces/invalid characters)? Can these 11 records match to themselves via lookup (seeing if it's something particular to Lookup)? IE: OLEDB source the original records then lookup themselves against themselves. I know, funky thing to check, but it'll let us know if its just the component or the data is somehow different between the two sources.
I'm not convinced the data is stored as expected, or being read from the other source as expected. A different dash, trailing spaces, an O instead of a 0 (I know, there are no Os in hex)... something like that. With both sides being UPPER()'d and both being DT_STR datatype with the same code page, I'd start inspecting the data.
Maybe add LTRIM and RTRIM to the queries, to make sure white space is removed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2012 at 5:41 pm
Have you considered just doing it in T-SQL?
INSERT INTO YourTable (C1,C2,C3)
SELECT C1,C2,C3
FROM
(Your outer join query to identify new rows)
You just run that in an execute SQL statement in SSIS (or an execute SQL step in a job, or in any tool that can submit a SQL query) and off it goes.
It has advantages and drawbacks.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply