Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select rows from table b not in table a?


Select rows from table b not in table a?

Author
Message
cbrinson
cbrinson
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 120
I have made a small amount of progress with learning SSIS. But I am struggling with it more than I have with any other tool, language etc. I don't know why. It just seems very unintuitive to me and a bit buggy?

I am trying to start simple. I have a flat file that I am successfully connecting to and successfully reading (only by setting Ignore Failure on all output columns). I also have a simple query I am running. ie. One Flat File Source and one OLE DB Source. I would like to select all the rows in query b that are not found in extract file a. I played around with Merge Join and got that to work. But I don't think that is what I need. What I really want is so easy in SQL.
SELECT * FROM tableA A
WHERE A.account_id NOT IN
(SELECT account_id
FROM tableB)

That is it. How could I do this very simple task in SSIS?

Thanks a ton in advance!

Chris
Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 414
Nope - pretty much bang on. Use the join, with sorted inputs, and left outer it.

For the inverse, you can use a lookup component.


To open an old can of worms, stay away from Where not in. It's non-sarg query and will hurt. Google it Smile




Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Michael Ross-468051
Michael Ross-468051
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 154
For small datasets, the Sort+Merge Join with an Outer Join is a good fit. It behaves most like Join syntax you'd be used to in T-SQL. You can then use a Conditional split to redirect rows that are in your flat file but not in your T-SQL with an expression like ISNULL(table_key_id). You can play with the Sort settings for your columns to change things like case sensitivity. You might also want to use a Derived Column transform before your Sort to clean-up data (i.e. TRIM(text_column)).

One other note, sort your smallest columns first, this can dramatically improve performance for large datasets.

Alternatively, you can do the same thing using a Lookup. This is definitely faster, but can be difficult if you are trying to match on text data.
Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 414
For the problem above, you could not use the lookup as the source for the lookup is a text file.

A tip on the sort and cleaning: Do what you can in SQL. Use an order by in SQL. It's faster than SSIS at it. Do any trims , joins etc in SQL. Trimming a string in SQL means that potentially, less data could arrive for SSIS to deal with. It is obviously a good thing...




Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18065
I'm not sure I understand why you say lookup wouldn't work with a text source (or a text reference). Albeit slower thanks to the lack of indexing, I don't see why it wouldn't work. Once you set up the source and/or the reference file to appropriate read said file, what does the internal structure of the file have to do with anything?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 414
Matt Miller (1/30/2008)
I'm not sure I understand why you say lookup wouldn't work with a text source (or a text reference). Albeit slower thanks to the lack of indexing, I don't see why it wouldn't work. Once you set up the source and/or the reference file to appropriate read said file, what does the internal structure of the file have to do with anything?


A lookup component needs an OLEDB source for it's reference data. It cannot use a flat file.

Regarding the indexing, by default a lookup component caches everything and once the cache is hot, external indexing means nothing. It's internal to the SSIS engine. If you use partial caching, then the index on the lookup query is critical.

Data from anywhere can "flow over" a lookup component. However, the component needs to get it's refernce data from OLE DB.




Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18065
It's those little things you don't notice until you want to use them... You're absolutely right.

Learned yet another tidbit.

Thanks!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
cbrinson
cbrinson
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 120
I am starting to get the hang of it now. Thanks guys.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search