none
Select in SSIS

    Question

  • Hi All-

    I have a requirement. I'm using oracle database as source where I'm selecting (lets say) employee id.

    I'm passing the employee id to next component which needs to look in to SQL Server table (emp details) which gives the employee details based on the employee ID from oracle database. Please suggest me on how to use it.

    the query is like

    Select empname, dept, address from emptable where empid = ?

    I can  use ole db command but that does not give the output which i need to transform to a flatfile.

    Please help me on how to achieve this in ssis.

    Thanks,

    Prabhu

    Wednesday, July 17, 2013 4:21 PM

Answers

All replies

  • This would be a "Lookup".  Please see: http://msdn.microsoft.com/en-us/library/ms141821.aspx

    • Proposed as answer by Koen Verbeeck Wednesday, July 17, 2013 8:48 PM
    Wednesday, July 17, 2013 4:45 PM
  • Hi Prabhu,

    The high level data flow pipeline will consists of following task:

    1. OLE db/ADO.NET source that has selects empid's from Oracle.
    2. drag a lookp up component, that matches on empid between Oracle and SQL table.
    3. drag a flat file destination to write the data ont it.

    Thanks, hsbal

    Wednesday, July 17, 2013 9:18 PM
  • Thanks Tom.

    Could you please help me on how to implement this?

    For my case,

    Select id, empname, address from emp table. Here i will map the Id from the source I'm getting, Now I need to fetch empname & address and to pass to the down component (Lets say a flat file destination).

    Please help me.

    Thursday, July 18, 2013 4:22 AM
  • Thanks Harrry.

    This i have done. But my requirement is

    Select id, empname, address from emp table. Here i will map the Id from the source I'm getting, Now I need to fetch empname & address and to pass to the down component (Lets say a flat file destination).

    How to achieve this?

    Thursday, July 18, 2013 4:23 AM
  • Hi,

    • Source component must retrieve ID.
    • Output from Source must be passed on to Look up transformation, where in the query window you wll write the below query.
    Select id, empname, address from emp
    • Go to the columns tab, and join ID with ID, and check the empname & address check boxes.
    • Provide the ouput from look up to destination component.

    Here is an example.

    http://www.katieandemil.com/ssis-lookup-transformation-example-sql-server


    Rajkumar


    • Edited by Rajkumar5055 Thursday, July 18, 2013 4:45 AM Addnl info
    • Marked as answer by Prabhuvincent Thursday, July 18, 2013 8:04 AM
    Thursday, July 18, 2013 4:43 AM