Post reply

i want to generate sequence number in SSIS

  • October 13, 2013 at 7:30 am

    #281340

    My input is

    EmpNoDepno

    1001 NULL

    1001 501

    1001 502

    1002 NULL

    1002 700

    1003 NULL

    My Desired output is

    EmpNoDepno Seqno

    1001 NULL 0

    1001 501 1

    1001 502 2

    1002 NULL 0

    1002 700 1

    1003 NULL 0

    Can anyone please help me in achieving this in SSIS......

    Thanks in advance..

  • October 13, 2013 at 8:11 am

    #1658121

    A brief Google search led me to this [/url]article.

    It's not difficult, but it's much easier (and almost certainly faster) if you can do this in T-SQL instead.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 13, 2013 at 8:52 am

    #1658123

    Phil Parkin (10/13/2013)

    A brief Google search led me to this [/url]article.

    It's not difficult, but it's much easier (and almost certainly faster) if you can do this in T-SQL instead.

    thanks for this.

    I wanted to achieve this in SSIS and through google i know we can achieve it in T-SQL.But i my desire is to achieve throught SSIS

  • October 13, 2013 at 9:10 am

    #1658124

    manibad (10/13/2013)

    Phil Parkin (10/13/2013)

    A brief Google search led me to this [/url]article.

    It's not difficult, but it's much easier (and almost certainly faster) if you can do this in T-SQL instead.

    thanks for this.

    I wanted to achieve this in SSIS and through google i know we can achieve it in T-SQL.But i my desire is to achieve throught SSIS

    That's fine. The link describes how.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 13, 2013 at 10:03 am

    #1658126

    manibad (10/13/2013)

    Phil Parkin (10/13/2013)

    A brief Google search led me to this [/url]article.

    It's not difficult, but it's much easier (and almost certainly faster) if you can do this in T-SQL instead.

    thanks for this.

    I wanted to achieve this in SSIS and through google i know we can achieve it in T-SQL.But i my desire is to achieve throught SSIS

    Two things on this... first, even the article that Phil cites states that it's a whole lot easier to do in T-SQL. Second, even with the technique in the article, it's still not actually being done by SSIS... it's being done by a script.

    --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)

  • October 13, 2013 at 10:28 am

    #1658131

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 13, 2013 at 1:25 pm

    #1658137

    Phil Parkin (10/13/2013)

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    Quite true. However, it's like saying that creating a CLR to do something with T-SQL is a "T-SQL Solution". Neither the CLR nor the script you speak of are a hack but they are not a "pure" solution. In this case, it's not an SSIS solution. It's a script solution that SSIS can use. 🙂

    --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)

  • October 15, 2013 at 11:21 am

    #1658627

    Phil Parkin (10/13/2013)

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    SSIS also natively provides the flexibility to use a T-SQL query in the data source that generates the same numbers (or from a staging table if the original data source can't be queried via T-sql). Same semantic grey area, no?

  • October 15, 2013 at 11:40 am

    #1658630

    Nevyn (10/15/2013)

    Phil Parkin (10/13/2013)

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    SSIS also natively provides the flexibility to use a T-SQL query in the data source that generates the same numbers (or from a staging table if the original data source can't be queried via T-sql). Same semantic grey area, no?

    I'd say that that was an even greyer shade than the original 🙂 I wonder whether Jeff would consider the use of T-SQL within SSIS to be ' done by a script' ...

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 15, 2013 at 5:44 pm

    #1658756

    Phil Parkin (10/15/2013)

    Nevyn (10/15/2013)

    Phil Parkin (10/13/2013)

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    SSIS also natively provides the flexibility to use a T-SQL query in the data source that generates the same numbers (or from a staging table if the original data source can't be queried via T-sql). Same semantic grey area, no?

    I'd say that that was an even greyer shade than the original 🙂 I wonder whether Jeff would consider the use of T-SQL within SSIS to be ' done by a script' ...

    BWAAA-HAAA!!!! It certainly would NOT be an "SSIS Only" solution. 😀

    --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)

  • October 15, 2013 at 5:45 pm

    #1658757

    Nevyn (10/15/2013)

    Phil Parkin (10/13/2013)

    ...it's still not actually being done by SSIS... it's being done by a script.

    Well, I think we are drifting into a grey semantic area here.

    SSIS natively provides the flexibility to use scripts as part of packages - they are fully integrated and not some sort of add-on or hack. In this case, the data pipeline runs through a script which processes each row in (a pre-sorted) order and uses variables to track row numbers. It's the pre-sort and row-by-row processing that slow things down, of course.

    SSIS also natively provides the flexibility to use a T-SQL query in the data source that generates the same numbers (or from a staging table if the original data source can't be queried via T-sql). Same semantic grey area, no?

    Yes!

    --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)

Viewing 11 posts - 1 through 10 (of 10 total)

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