locked
How can I read a file and then update before insert to table? RRS feed

  • Question

  • I have a SSIS process that reads a file for a bulk load but requires a id that the application adds +1.  The issue I have is how in SSIS can I get the max id from destination and inject that into the flow between reading the file and inserting into table?

    File:
    NAME
    TEST
    TEST
    TEST

    GET MAX NUM = 3

    Insert End Result:
    NAME, NUM
    TEST, 4
    TEST, 5
    TEST, 6

    Does this makes since?  I know this is a crapy explanation :) I hope someone understands :).


    Thanks, Matt
    Thursday, April 21, 2011 9:35 PM

Answers

  • Use an Execute SQL Task prior to the Data Flow Task you use to read the text file.  Read the MAX value and store it in an SSIS variable.  In the Data Flow, you'll have to use a script or Row Numbering transform to add the sequence number to your flow.
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Todd C Friday, April 22, 2011 1:18 AM
    • Marked as answer by Tony Chain Tuesday, May 3, 2011 9:29 AM
    Thursday, April 21, 2011 9:55 PM

All replies