Post reply

What DFT to use to get data from external database

  • March 13, 2007 at 6:16 am

    #116597

    Hi

     

    I'm quite new to SQL Server and Integration Services and need help.

    I have one smalltable that is on SQL Sever with normally not more than 1000 rows. The other one is on the Oracle database with over 1000k of rows.

    f.example

    tbl1

    id, firstname, lastname

    tbl2

    id, city, zip

    So I want to get results from tbl2 where tbl1.id = tbl2.id

    It's easy to join these tables if they both are in f.ex SQL Server. But how do I do this when tbl2 is on Oracle server? How do I pass tbl1.id to oracle?

    I hope my question is clear and thanks for any help

  • March 13, 2007 at 6:27 am

    #694648

    Hi Vitali,

    You would use the Merge Join transformation in SSIS.

    In order to use this component, both the meta data and sort columns need to be the same for both inputs.

    Kindest Regards,

    Frank Bazan

  • March 13, 2007 at 8:13 am

    #694697

    Hi

    Thanks for your reply. I've tried Merge Join. The problem with MJ is that it first reads all rows from an Oracle table and then merges them. Even if I limit the oracle data by a query, I still have to wait for 2-300k rows to be loaded. I thought it is possible to pass criteria to Oracle somehow so only requested rows are transfered from Oracle to SQL Server.

    any ideas? Maybe i have used MJ in a wrong way?

    Regards

    Vitali

  • March 13, 2007 at 12:18 pm

    #694781

    If its a simple inner join you could use a lookup transform and use your SQL OLE DB datasource as a reference. Any records that are found will come through the pipeline?

    Kindest Regards,

    Frank Bazan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply