locked
Increment Value RRS feed

  • Question

  • I have Excel file.
    From Excel file I need to get data to the table.
    I can create SSIS process for that and run it as a job.
    Table has version id column.

    Every time job runs and data inserts to the table version must increment.
    For example today I run and all records will be with Version ID = 1.
    Tomorrow all the records from next file will be inserted with Version Id = 2.

    How do I do that?

    How do I create a process in SSIS tool for that?

    Thank you.


    V. A.
    Thursday, February 18, 2010 8:59 PM

Answers

  • Place an Execute SQL Task before all other Tasks in your Control Flow, and issue a MAX(ID) query to retrieve the "last" ID used from that table.  Store the value in an SSIS variable.

    In your Data Flow where you're loading the table, use a Derived Column component to add (or amend) the "ID" column to the flow, using the SSIS variable + 1.
    Todd McDermid's Blog
    • Marked as answer by vita3 Thursday, February 18, 2010 9:56 PM
    Thursday, February 18, 2010 9:24 PM
  • there are several ways to do it

    1- User variables and expressions are your answer.
    So you can increment it every day.
    2- Or use dtsconfig file
    3- Or just create  a lookup table with processed column
       so you can get max processed column.

    • Marked as answer by vita3 Thursday, February 18, 2010 9:57 PM
    Thursday, February 18, 2010 9:25 PM

All replies

  • Place an Execute SQL Task before all other Tasks in your Control Flow, and issue a MAX(ID) query to retrieve the "last" ID used from that table.  Store the value in an SSIS variable.

    In your Data Flow where you're loading the table, use a Derived Column component to add (or amend) the "ID" column to the flow, using the SSIS variable + 1.
    Todd McDermid's Blog
    • Marked as answer by vita3 Thursday, February 18, 2010 9:56 PM
    Thursday, February 18, 2010 9:24 PM
  • there are several ways to do it

    1- User variables and expressions are your answer.
    So you can increment it every day.
    2- Or use dtsconfig file
    3- Or just create  a lookup table with processed column
       so you can get max processed column.

    • Marked as answer by vita3 Thursday, February 18, 2010 9:57 PM
    Thursday, February 18, 2010 9:25 PM