When Etlworks Integrator reads data in a specific data exchange format, for example, CSV, XML, JSON, etc., it samples the data sets and sets the column data type using the best estimate. For example, if the value of the column in the several sequential rows appears to be numeric, the system sets the data type for this column to numeric for the rest of the data set.
It works very well, but in some rare cases, the string columns are wrongly recognized as numeric, which is causing issues when loading data into the destination (typically a database). For example, let's consider a data set like below:
id, first_name, last_name
1, Joe, Doe
2, Jane, Doe
AB, Sima, The Dog
Based on the data in the first 2 records, the system sets the data type for the column id to numeric and automatically creates a table in the database with this data type. When it's trying to load the third row, with id set to AB, it fails with an exception because the database expects that column id has a numeric data type.
The recommended solution is to manually set the column data type in the mapping.
In some cases, specifically, when loading data in some sort of staging table, it is easier to configure the data exchange format with the property All fields are strings enabled. In this case, the system will create all fields with the string (VARCHAR) data type. The length of the field will be automatically set to the maximum allowed length for the VARCHAR fields for the specific database.