I have a simple process that reads data from a MS SQL DB and maps it to a flat file and places it in a destination folder. Looks like this:
The process runs just fine and produces a file in the designated format. Data in some positions are missing, but that is because it is not present in the data output from the DB:
#|10005|^|aName|^||^|9900|^|SomeRoad 20 |^||^|Frederikshavn|^|999999999999|^||^|88888888|^||^|9999999|^|0E-20|^|81.80000000000000000000|^|0|^|LM+10D|^|99999999|
Notice that there are 16 data separators "^" in the above, aka 16 fields. This matches the source and destination profiles.
The problem is that for some records, the field [City], which in the above case is "Frederikshavn", contains a special character (Danish "ø", "æ" and "å"), for example "Sorø" and this seems to corrupt the data from the DB when it enters Boomi. A record with the city name "Sorø" looks like this in the output from the DB:
#|100002|^|anotherName|^|Att.: PersonA|^|4180|^|A City Road 54, app 3|^||^|Sor輞||^||^|666666336|^||^||^|0E-20|^|0E-20|^|0|^|LM+10D|^||
For this record, there are only 15 data separators "^". So a field / position is missing from the record. From the DB, Boomi reads the name "Sorø" as "Sor輞" but it seems that both a "|" data qualifer and "^" a data separator are missing after this.
I expect the data "^|Sor輞||^" to be "^|Sor輞|^||^" to be the same format as "^|Frederikshavn|^|999999999999|^". This of course corrupts this record and others like it in the flat file output, as fields / positions are missing for these records.
I have tried searching for an answer with the keyword "Special characters", but the obvious hits French Data has Converted into Some Special characters and Foreign Language Characters are not translated does not help me. How do I change File Encoding?