Setting a break key on an integer field in Data Services 12
I'm using Data Services 12 and I'm assigning a record id number to each record that I read from an input file.
I'm then doing some splits on the records to create multiple copies of the same record.
Now it's time to recombine these records and eliminate any duplicates. Some records might be unique so I need to use a match transform here instead of a merge or query transform.
Since I only want to compare records i nthe match transform that were from the original record I'm breaking on the record id number that I created eariler in the data flow. However, I'm getting a warning during the job execution.
The warning states that the break field "individual_id" has a starting position set or defaulted to "1" and a length set or defaulted to "10" but the available length is "4".
This warning makes sense becuase the true data length for an INT field is SQL Server only consumes 4 bytes. But why is the system giving me this warning? I still want it to look at and use all 10 numeric positions of the integer value to make my break groups.
Paula Augedahl replied
This is actually reported as an error in the initial release of Data Services. When you and I worked a case revolving around this in SMP, I wrote this up to development and it is expected to be fixed in the next release, barring any unforeseen changes.
The INT datatype is converted to VARCHAR and incorrectly set to 4 bytes in length.
As a workaround, you can use a Query transform to convert this field to a VARCHAR format prior to Match and this should yield the correct input for the break key.
Hope this helps,