locked
Joining 2 tables (inner join) with Integration services RRS feed

  • Question

  • Hi:

    I am trying to join 2 tables (Inner join) using  Merge join but I am not getting any records after the join although I am 100% sure there are common values.

    Is there any reason for this? 

    These 2 tables are in different server but both are SQL server 2005 DBs and the columns have the same name and data type.
    Friday, October 16, 2009 3:15 PM

Answers

  • Hi Phil:

    1 - Yes, I am doing the second option.
    2 - I am doing the join usinc numeric columns.  I ran a query in the DB and I am getting records without any transformation.

    Thanks,

    Just so I'm clear - you are using the IsSorted=True property on the OLE DB Source components?

    Do you actually have an ORDER BY statement in the SQL that you use to extract the data?  If you tell the OLE DB Source component that the data is sorted, the data actually *has* to be sorted.  It won't do it for you.

    And if the data types are indeed numeric in the data flow (DT_I4, etc...) then obviously you don't have to worry about trailing spaces/case-sensitivity.  You do however need to ensure that both sides of the merge join component are sorted correctly (one can't be ascending and the other descending, for instance, and they need to be sorted on the join key, not some other column)
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 7:56 PM

All replies

  • 1 - Are you using the sort components before the merge join, or are you telling the OLE DB Sources that the data is sorted?
    2 - The Merge Join component will enforce case-sensitivity and trailing spaces.  You may want to try (before the sort components if you are using them) adding a derived column to do something like this on your key columns:  UPPER(TRIM([KeyColumn]))


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 3:28 PM
  • Hi Camezaa

    Why are you joining 2 tables in SSIS? How big are they?

    I think you should create a join in SQL instead and use linked servers.

    Friday, October 16, 2009 7:45 PM
  • Hi Phil:

    1 - Yes, I am doing the second option.
    2 - I am doing the join usinc numeric columns.  I ran a query in the DB and I am getting records without any transformation.

    Thanks,
    Friday, October 16, 2009 7:52 PM
  • Hi Phil:

    1 - Yes, I am doing the second option.
    2 - I am doing the join usinc numeric columns.  I ran a query in the DB and I am getting records without any transformation.

    Thanks,

    Just so I'm clear - you are using the IsSorted=True property on the OLE DB Source components?

    Do you actually have an ORDER BY statement in the SQL that you use to extract the data?  If you tell the OLE DB Source component that the data is sorted, the data actually *has* to be sorted.  It won't do it for you.

    And if the data types are indeed numeric in the data flow (DT_I4, etc...) then obviously you don't have to worry about trailing spaces/case-sensitivity.  You do however need to ensure that both sides of the merge join component are sorted correctly (one can't be ascending and the other descending, for instance, and they need to be sorted on the join key, not some other column)
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 7:56 PM
  • Hi Camezaa

    Why are you joining 2 tables in SSIS? How big are they?

    I think you should create a join in SQL instead and use linked servers.


    Linked servers tends to perform poorly. Chances are a merge join perform better.

    My personal preference is to dump data in a staging area and then make the join via t-sql.

    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Friday, October 16, 2009 7:59 PM
  • If you are telling the OLE DB sources the data is sorted you have to include a ORDER BY clause in your query. Just checking that is the case.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Friday, October 16, 2009 8:00 PM