Use the following rules and guidelines for datatypes and conversion:
• The default datatype for all fields in flat files is Nstring(255).
If you need to change a datatype, use the Edit Metadata option to edit the native type or precision for the field.
• The task may have unexpected results if you map incompatible datatypes between source and target fields or between the output of an expression and a target field.
For example, if you map a datetime column of a MySQL database source to an integer column of a Salesforce target in a synchronization task, the task fails.
• Numeric data with a precision greater than 28 is truncated to a precision of 15 when written to a target.
For example, 4567823451237864532451.12345678 might be written to the target as 4567823451237864000000.00000000.
• A synchronization task fails when you map source fields of the following datatype and database type to a Salesforce target:
Datatype
Database type
tinyint
MySQL
tinyint
SQL Server
interval year to month
Oracle
interval day to second
Oracle
rowid
Oracle
urowid
Oracle
• In most cases, the replication task creates the same precision and scale in the target as the source. In other cases, the replication task uses a different precision or scale:
- In some cases, the replication task does not specify the precision or scale, and the database uses the defaults. For example, the source is MySQL and source datatype is Double(12). The replication task creates the Number datatype in an Oracle target and does not specify the precision.
- If the source is not Oracle, the target is Oracle, and the source datatype is nchar, nvarchar, or nclob, the application multiples the source field precision by 2, up to a maximum of 64000, to obtain the target field precision.
- If the source is MySQL, the target is Microsoft SQL Server 2000 or 2005, and the source datatype is date or time, the target field datatype is Timestamp(23, 3).
• If a replication task writes data from MySQL to a flat file and the source contains time data, Data Integration converts the time data to a date/time datatype, where the date is the current date and the time is the time specified in the source.
You can use a string function in an expression to remove the date before loading the flat file.
•For an Oracle source or target, the precision of a Number field must be greater than or equal to the scale. Otherwise, the task fails.
• When a synchronization task writes 17-digit or 18-digit numeric data with no scale from Salesforce to an Oracle column with a Number datatype, the task may produce unexpected output in the target.
For example, the synchronization task writes the Salesforce value 67890123456789045 as 67890123456789048 in an Oracle target.
•When you use an ODBC connection for an Oracle database target, ensure that the maximum precision for an Oracle table column does not exceed the following values: char(1999), varchar(3999), nvarchar(3998), and nchar(3998).
•A task may load corrupt data into the target if the data comes from a source field of the Real datatype from Microsoft SQL Server.