Generating a range of surrogate keys
Posted: Fri Feb 24, 2012 6:54 am
We were using a Surrogate Key Generator, but this proved unreliable - the start of our range is determined externally and we have a block of values allocated to us, we need to generate the exact keys in that range.
I am trying with a Transformer stage set to run in Sequential mode, with stage variables but the job is freezing.
The Transformer does a Sort Merge on a 300-character input column to make sure the SKs are generated in a predictable order. In my test run this column just contains numbers, which when sorted should be 1, 10, 100, 1000, 10000, 10001, 10002, etc., and there are 100,000 records.
Any idea why my job would just stop with 100000 on the input link to the Transformer and 0 on the output? The only expressions in the Transformer are these Stage Variables:
svSeq (Integer) = If @INROWNUM = 1 Then pStart Else If svSeq = 999999999 Then 1 Else svSeq + 1
svDIN (BigInt) = DateToString(CurrentDate(),"%yyyy%mm") : Right( '0000':pDataSource,4) : Right( '000000000' : svSeq, 9 )
pStart and pDataSource are job parameters.
I just tried taking the sort off of the input link and now it gets as far as 862 rows on the input and 484 on the output, and then stops. The next stage executes in Parallel mode, has Auto partitoning.
I am trying with a Transformer stage set to run in Sequential mode, with stage variables but the job is freezing.
The Transformer does a Sort Merge on a 300-character input column to make sure the SKs are generated in a predictable order. In my test run this column just contains numbers, which when sorted should be 1, 10, 100, 1000, 10000, 10001, 10002, etc., and there are 100,000 records.
Any idea why my job would just stop with 100000 on the input link to the Transformer and 0 on the output? The only expressions in the Transformer are these Stage Variables:
svSeq (Integer) = If @INROWNUM = 1 Then pStart Else If svSeq = 999999999 Then 1 Else svSeq + 1
svDIN (BigInt) = DateToString(CurrentDate(),"%yyyy%mm") : Right( '0000':pDataSource,4) : Right( '000000000' : svSeq, 9 )
pStart and pDataSource are job parameters.
I just tried taking the sort off of the input link and now it gets as far as 862 rows on the input and 484 on the output, and then stops. The next stage executes in Parallel mode, has Auto partitoning.