SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


i want to generate sequence number in SSIS


i want to generate sequence number in SSIS

Author
Message
manibad
manibad
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 143
My input is

EmpNo Depno
1001 NULL
1001 501
1001 502
1002 NULL
1002 700
1003 NULL

My Desired output is

EmpNo Depno 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..
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53606 Visits: 21207
A brief Google search led me to this article.

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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
manibad
manibad
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 143
Phil Parkin (10/13/2013)
A brief Google search led me to this 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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53606 Visits: 21207
manibad (10/13/2013)
Phil Parkin (10/13/2013)
A brief Google search led me to this 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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222072 Visits: 42003
manibad (10/13/2013)
Phil Parkin (10/13/2013)
A brief Google search led me to this 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53606 Visits: 21207
...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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222072 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3636 Visits: 3149
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53606 Visits: 21207
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' ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222072 Visits: 42003
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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search