locked
Perform an except or not in operation in SSIS between two tables one from an ODBC connection and one from OLE DB RRS feed

  • Question

  • Hi,

    I have two tables : A - from an OLE DB connection and B from an ODBC connection.

    they both have a key field lets name it "ID".

    I want to find  which "ID"s from table A are missing from table B.

    I understand I can not use a lookup transform because it can only get OLE DB and not ODBC connection.

    What is the simplest way to do this?

    Thanks.


    • Edited by BI_dev_n Wednesday, September 7, 2016 11:07 AM
    Wednesday, September 7, 2016 11:06 AM

Answers

  • Hi

    Hi, there are a few ways to do this,

    You could import all the keys from both tables into staging tables, then use T-SQL to to do the comparison using any method you liked (Left join,Except, not in .....)

    You would also be able to do something similar in SSIS using the merge join. This requires sorted inputs which may cause problems on large unsorted data sources.

    • Proposed as answer by Eric__Zhang Thursday, September 8, 2016 1:45 AM
    • Marked as answer by Eric__Zhang Sunday, September 25, 2016 11:58 AM
    Wednesday, September 7, 2016 12:03 PM
  • Hi

    I think I have found a way 

    I used a cach transform on the query result from the odbc connection

    then I can use the Lookup transform with a cache connection.

    I am testing it now. 

    • Proposed as answer by Seif Wang Saturday, September 24, 2016 12:54 PM
    • Marked as answer by Eric__Zhang Sunday, September 25, 2016 11:58 AM
    Wednesday, September 7, 2016 12:26 PM

All replies

  • Hi

    Hi, there are a few ways to do this,

    You could import all the keys from both tables into staging tables, then use T-SQL to to do the comparison using any method you liked (Left join,Except, not in .....)

    You would also be able to do something similar in SSIS using the merge join. This requires sorted inputs which may cause problems on large unsorted data sources.

    • Proposed as answer by Eric__Zhang Thursday, September 8, 2016 1:45 AM
    • Marked as answer by Eric__Zhang Sunday, September 25, 2016 11:58 AM
    Wednesday, September 7, 2016 12:03 PM
  • Hi

    I think I have found a way 

    I used a cach transform on the query result from the odbc connection

    then I can use the Lookup transform with a cache connection.

    I am testing it now. 

    • Proposed as answer by Seif Wang Saturday, September 24, 2016 12:54 PM
    • Marked as answer by Eric__Zhang Sunday, September 25, 2016 11:58 AM
    Wednesday, September 7, 2016 12:26 PM