DSXchange

I want to generate sequence numbers starting from 1 every time the job run.
Number should be generated in sequence without gaps in 4 node job.

Thing i have already tried
1) (@inrownum - 1) * @numpartitions + @partitionnum + 1
The above formula only works in 2 node.

2) Column generator - It creates gaps between numbers in 4 nodes and the numbers are not in sequence.

3) Surrogate Key - It takes the last highest value and starts from there. I want it to start from 1 for each job run. Is there any way to reset the key ?

Any suggestions is welcome.

Thanks in Advance.

In what way does the given expression "not work" with four nodes?

In 4 nodes the numbers are not generated in sequence. It leaves gaps in between.

Did you use round robin partitioning method?

Mike

rish0201 wrote:I want it to start from 1 for each job run.

Then it would seem you do not have a need for any kind of surrogate key 'generator' but simply need a counter. At its most simple, you could use a stage variable for that, initial value of zero, increment for each record... as long as you handled the partitioning properly.

As inferred by @Mike as well this method requires data to be evenly distributed across all partition hence it works well with Round Robin partitioning. If not it will generate sequence with "gaps" (i.e. what you are observing).

Use other methods as suggested by craig.

1) Use Stage variable.

2) If source is seq. file stage generate row number.

3) If database use row_number function.

4) Enable transformer stage to run on a single node and use @INROWNUM

Hi,

You weren't far out with your original idea.

Here's what I use: (@PARTITIONNUM+1)*10 : @OUTROWNUM

Use @OUTROWNUM rather than @INROWNUM to aviod problems with funnel stages etc.

Hope that helps!

Cheers,
Bob.