Setting a break key on an integer field in Data Services 12

[richard_west]

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 · Accepted solution]

Richard,

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,

Paula

[Former Member · Accepted solution]

Hello Paula,

Well, I need help with one issue. I am trying to find duplicates with Match Transform. I want to find duplicates for PersonalID numbers which are set in the SQL database as decimal (18,0) but acutally they are just numbers with 13 characters (e.g. 123456789112).

I use Base Match and i want to select in Match Criteria Editor -> Criteria Fields Tab -> Available criteria -> Custom. For Custom criteria field, i can only choose varchar input fields, and i cannot use int or decimal.

I i use for example IndividualID_MatchBatch then i can use decimal PersonalID but is limited to length 10, and there is not way to change it to 13.

So, could you please help me to find solution to find numeric duplicates with Match Transform.

Thank you very much in advance.

Maria

[Former Member]

Hello all,

Well, I need help with one issue. I am trying to find duplicates with Match Transform. I want to find duplicates for PersonalID numbers which are set in the SQL database as decimal (18,0) but acutally they are just numbers with 13 characters (e.g. 123456789112).

I use Base Match and i want to select in Match Criteria Editor -> Criteria Fields Tab -> Available criteria -> Custom. For Custom criteria field, i can only choose varchar input fields, and i cannot use int or decimal.

I i use for example IndividualID_MatchBatch then i can use decimal PersonalID but is limited to length 10, and there is not way to change it to 13.

So, could you please help me to find solution to find numeric duplicates with Match Transform.

Thank you very much in advance.

Maria

[richard_west]

Thanks Paula.

I'll continue to work with the data as a varchar until the update comes along