none
SSIS package to get a value from a table based on 2 fields in a flat file

    Dotaz

  • Hello,

    I'm working on an SSIS package that takes a flat file as a source, looks up a value in a table based on 2 fields in the flat file record and writes the fields from the flat file plus the value retrieved to a destination table.  What would be the best way to do this?

    11. června 2018 19:40

Odpovědi

  • as you state what would require is this

    Standard Data flow task with

    1. Flat file source -pointing to your file

    2. Lookup task - lookup with your table on required columns

    3. OLEDB destination - pointing to your table 

    You need to join the lookup match output to OLEDB destination

    Also based on size of your reference table used in lookup you can use full cache or partial cache mode.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď pdconway 15. června 2018 12:29
    11. června 2018 19:46
  • Yes, you need to use the "Lookup Transformation" from the SSIS toolbox. Also, in the connection tab of Lookup Transformation Editor dialogue box, you need to use the result of an SQL query (last radio button) option. In the query only retrieve the lookup column and the value you want to fetch from the query. If the query is too complicated and results in a huge data set then you may want to cache or partially cache the result for better performance.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    11. června 2018 20:44

Všechny reakce

  • as you state what would require is this

    Standard Data flow task with

    1. Flat file source -pointing to your file

    2. Lookup task - lookup with your table on required columns

    3. OLEDB destination - pointing to your table 

    You need to join the lookup match output to OLEDB destination

    Also based on size of your reference table used in lookup you can use full cache or partial cache mode.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď pdconway 15. června 2018 12:29
    11. června 2018 19:46
  • Sorry, I guess I oversimplified it.  The lookup actually is a query that joins several tables to come up with the value.  By lookup task, do you mean Lookup Transformation?  And would I use the results of a sql query as opposed to a table in that transformation?
    11. června 2018 20:18
  • Yes, you need to use the "Lookup Transformation" from the SSIS toolbox. Also, in the connection tab of Lookup Transformation Editor dialogue box, you need to use the result of an SQL query (last radio button) option. In the query only retrieve the lookup column and the value you want to fetch from the query. If the query is too complicated and results in a huge data set then you may want to cache or partially cache the result for better performance.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    11. června 2018 20:44