February 7, 2011 at 8:39 am
I have setup a simple Data Flow (OLE DB Source + Lookup ) to test Lookup
for OLE DB Source (DB is AdventureWorks and table is Person.Address)
for LOOKUP task ((DB is AdventureWorks and SQL query "SELECT * FROM Person.Address" liked with AddressID on both sides and output is all on right side)
and this is working fine.
BUT when I add "WHERE AddressID=1" to the SQL query of the LOOKUP. system crashes.
Any special reasons for this !?
I am using SQL 2005 Dev under Windows 7.
Thank you! :angry:
February 7, 2011 at 8:57 am
When you say "system crashes" do you literally mean the computer shuts down, or something else? (That's what I'm used to thinking of as a "system crash".)
Is it possible the lookup is returning zero rows based on that query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 10:40 am
Thanks for the reply.
Sorry for not being clear. The error message is "[Lookup [247]] Error: Row yielded no match during lookup. "
But when I Preview under Lookup there is one row.
As I mentioned when the filter "WHERE ...." is removed, it is working again.
Thanks again.
February 7, 2011 at 10:55 am
Does the one row in the lookup match any of the rows you're using it as a lookup for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 11:06 am
Yes, both of the data come from the same database and table.
OLE DB has 100% data and Lookup has a filter to restrict to only one line (for testing purpose).
One extra question here - IF there is NO match (zero row returned), the BIDS is supposed to give out error message and goes down.
BUT the above question DOES not apply to my case.
Thanks.
February 7, 2011 at 11:24 am
The error you're getting is "no match during lookup". Have you looked at the data before that step to make sure it's what you think it is? (You can set a dataset review on the flows in BIDS, for debugging purposes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 11:41 am
By default if any of the input rows into a lookup don't match the step will fail with the error you are getting. If you go open the lookup and go to advanced -> configure error output you should be able to change this setting to either redirect the rows that don't match or to ignore them.
February 7, 2011 at 11:57 am
One more hero to come for the rescue. Thanks.
I have done a simple test (you can put on any testing database)
This to create a simple table and data filling
create table t1 (
ID int,
Name varchar(100))
insert into t1 values (1, 'Name001')
insert into t1 values (2, 'Name002')
insert into t1 values (3, 'Name003')
insert into t1 values (4, 'Name004')
insert into t1 values (5, 'Name005')
One Data Flow under the Control FLow
Data flow has one OLE DB (DB with table t1) and one Lookup (SQL Query "SELECT ID, Name FROM dbo.t1")
This works and returns all the five rows.
BUT this DOES NOT and "crashes"
Data flow has one OLE DB (DB with table t1) and one Lookup (SQL Query "SELECT ID, Name FROM dbo.t1 WHERE ID=1 ")
Same error message "[Lookup [37]] Error: Row yielded no match during lookup. "
Why ????
I need water to refresh myself and for this fire too!!!
Thank you gentlemen!
February 7, 2011 at 12:54 pm
You have the step set to fail if any rows aren't matched. That's the default. Change the error handling in the step.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 2:06 pm
Thanks for the time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply