Unable to lookup complete resultset on uniqueident

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Unable to lookup complete resultset on uniqueident

Author

Topic

okshilato
Starting Member

2 Posts

Posted - 2009-10-20 : 00:12:12

Hi,

I'm having trouble to get complete resultset from a Lookup component between 2 tables having join keys as uniqueidentifier datatype.

In Management Studio using SQL statement on the same tables (inner join) on the same keys I've got 36 rows.
On SSIS 2008, using Lookup component on the same keys I've got only 1 row and nothing else more on any output from the component. I've tried more than a half a day to figure out what's happening...but still not found enything....getting very frustrated here.

Does anybody has the same problem before...?

Help will be very, very, very appreciated...

Cheers,
Oks

SQL SERVER 2008 ENTR EDITION x64

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-20 : 06:17:26

I've not come across this before, but I don't use the uniqueidentifier datatype.
Can you post a sample of the keys, including the one that matches.
And I'll see if I get the same here.

okshilato
Starting Member

2 Posts

Posted - 2009-10-20 : 11:32:10

Hi YellowBug,

Thank you for your answer.

Here is some sample of uniqueidentifier datatype coming from the database:

DB3F6DE1-6E74-4CD3-91AD-A6282B14C194
12303FF4-3D45-4294-8411-7013F58A82A7
4EB0F5D9-F965-4490-83CB-BAAD82D99505
...etc

The key columns from both tables are not string...they have uniqueidentifier datatype coming from the database...that's how my source tables has been designed initialy, not by me.

When I use Lookup component on those keys...the resultset is incomplete (1 row).
When I used those keys in SQL statement with INNER JOIN...I'got the complete resultset (36 rows).

I don't understant why Loookup component is behaving in this manner.

Thank you for any advice....getting frustrated here.

Cheers,
Oks

SQL SERVER 2008 ENTR EDITION x64

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-20 : 13:20:31

What did you select for "Specify how to handle rows with no matching entries" ?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-20 : 16:46:21

The lookup works for me with uniqueidentifier datatype in the join.

This is the Lookup1.SqlCommandParam. Compare to yours:


select * from (select * from [dbo].[Table_UI_2]) [refTable]
where [refTable].[KeyID] = ?

Have you used the Data Viewer to see the data flow?

What is "special" about the one row that matches?

Sorry I can't be of more help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources