Post reply

Flat file to Table

  • July 22, 2009 at 2:33 pm

    #1028674

    Ah, so you only do the comparison if the row already exists? Is this the correct logical flow:

    1. Check for Existance of row

    2. If not exist, insert.

    3. If exist, compare difference between existing tax_val and new tax_val to range value.

    4. If within range, update existing row. If not, do nothing.

    Is this accurate?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 22, 2009 at 9:32 pm

    #1028748

    John Rowan (7/22/2009)

    OK, here's what I'd do:

    Create a Data Flow with the following contents:

    1. Flat File Source adapter to get your file info into the data flow.

    2. Lookup Transform. Use the City value in your data flow to get the range values from your other table. Configure the Lookup Transform to add the range values into your data flow.

    3. Conditional Split. Use the expression builder to evaluate the comparison between the Tax_val and range values. You'll end up w/ 2 outputs. One that contains the rows you want to work with and the other that contains the rows you don't need.

    4. Lookup Transform #2. Send the rows that you want to work with from the output from step 3 above into this Lookup. Use it to look up your row to determine if the row needs inserted or updated.

    5. OLE DB Destination. Send the Red arrow from your Lookup above into this destination. You'll be prompted to change the error output config. Set the error output to redirect the rows. Configure your destination to insert the rows into your table.

    6. OLE DB Command. Send the Green arrow from your Lookup above into this transformation. Write a SQL Statement to UPDATE your rows...UPDATE YourTable SET YourColumn=? WHERE......use the column mappings tab to map your ? parameter to a data flow column.

    That should do what you're looking to do. Post back any comments/questions/concerns in this thread.

    Heh... and now we know why I don't even bother with SSIS (or DTS). THAT's supposed to be easier and faster than a nice little ol' BULK INSERT within an UPSERT proc? I don't think so... :blink: 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 23, 2009 at 6:46 am

    #1028898

    Yes you got it right. These are the logical steps.

  • July 23, 2009 at 9:34 am

    #1029075

    Jeff Moden (7/22/2009)

    Heh... and now we know why I don't even bother with SSIS (or DTS). THAT's supposed to be easier and faster than a nice little ol' BULK INSERT within an UPSERT proc? I don't think so... :blink: 😛

    But stored procedures don't have pretty colored arrows:Whistling:

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 4 posts - 16 through 18 (of 18 total)

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