Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

i want to generate sequence number in SSIS Expand / Collapse
Author
Message
Posted Sunday, October 13, 2013 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, 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..
Post #1504263
Posted Sunday, October 13, 2013 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1504267
Posted Sunday, October 13, 2013 8:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, 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
Post #1504269
Posted Sunday, October 13, 2013 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1504270
Posted Sunday, October 13, 2013 10:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 35,772, Visits: 32,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504272
Posted Sunday, October 13, 2013 10:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
...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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1504277
Posted Sunday, October 13, 2013 1:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 35,772, Visits: 32,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504285
Posted Tuesday, October 15, 2013 11:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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?
Post #1504877
Posted Tuesday, October 15, 2013 11:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1504882
Posted Tuesday, October 15, 2013 5:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 35,772, Visits: 32,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1505026
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse