locked
Script to increment a value using ssis RRS feed

  • Question

  • Hi,
    I'm newbie to vb.net coding.
    Can any body send me the code for incrementing a value by 1 
    Ex: I have 3 columns say ID,Date,PO_NO

    ID   DATE            PO_NO
    1     getdate()        112
    1     getdate()        113
    1     getdate()        114

    2     getdate()        213
    2     getdate()        214

    3     getdate()        421
    3     getdate()        422
    3     getdate()        423
    3     getdate()        424

    Now if I insert new rows between these  records  like.....
     I want my output shud be (which is in BOLD)
    ouput:

    1     getdate()        112
    1     getdate()        113
    1     getdate()        114
    1     getdate()     1001

    2     getdate()        213
    2     getdate()        214
    2     getdate()     1002

    3     getdate()        421
    3     getdate()        422
    3     getdate()        423
    3     getdate()        424
    3     getdate()     1003


    Thanks in Advance!

    RH



    sql
    Monday, May 4, 2009 8:20 PM

Answers

  • You don't "need" a script to do this - although you could use one if you wanted.

    As it looks to me, you want to add a new record to your flow for every ID.  Each row you create should have an increasing "sequence number".

    So - Multicast your data flow.
    Send output #1 to a Sort OR Aggregate component.  Sort by ID, removing duplicates, and only pass through the ID column - or Aggregate (group by) ID.  Send the Sort/Aggregate output to a Derived Column transform to add the date column, then to a Row Number transform or a Script component to add the sequence number.
    Send the Row Number/Script output to a Union All component.  Send the Multicast output to the Union All component as well.
    Todd McDermid's Blog
    • Marked as answer by Bob Bojanic Thursday, May 7, 2009 5:54 PM
    Monday, May 4, 2009 8:45 PM