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 @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
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 @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
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: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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 @ 1:56 AM
Points: 5,101, Visits: 11,901
...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.
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: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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: Wednesday, September 24, 2014 8:15 AM
Points: 628, Visits: 2,129
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 @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
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: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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