Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select rows from table b not in table a? Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2008 1:57 PM
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
Post #448457
Posted Wednesday, January 30, 2008 8:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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 :)





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!
Post #449443
Posted Wednesday, January 30, 2008 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 9, 2013 10:10 AM
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.
Post #449466
Posted Wednesday, January 30, 2008 9:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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!
Post #449477
Posted Wednesday, January 30, 2008 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #449523
Posted Wednesday, January 30, 2008 10:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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!
Post #449529
Posted Wednesday, January 30, 2008 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #449543
Posted Thursday, January 31, 2008 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2008 1:57 PM
Points: 28, Visits: 120
I am starting to get the hang of it now. Thanks guys.
Post #450022
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse