I am having a requirement where I need to count the number of records and do a math processing on the basis of even numbers, odd numbers etc. Now as we know that in multi node architecture the counts will always be initiated from 1 for all the partitions. But the problem is I need to do it in the transformer without adding any extra stage, because it is a requirement from client.
Now one approach I tried was to set the Transformer to partition type as "ENTIRE" and then constraint on @PARTITIONNUM = 1 and then all the records will give me correct count. But this result in sacrificing the parallel processing as per my client which I do not understand why.
Second approach was to use NextSurrogateKey(), But this is also not consistent because the surrogate key is never generated in a contiguous sequence. For one partition the values start from 1 where as for another partition the values start from 1000.
So after going through many posts finally I got some pointers from Vincent McBurney's post where he sugeested, something like this: -
Code: Select all
1. Define a Stage Variable svCounter and initiate it with value = @PARTITIONNUM-@NUMPARTITIONS+1.
2. Now the same stage variable be incremented with logic as svCounter = svCounter+@NUMPARTITONS.
So the summary is I am not able to get what is required. Can any one help in this case. I am not sure whether multi node processing can hamper such a basic use of stage variables.