Function Reference > Datatype reference > Datatype conversion for replication tasks
  

Datatype conversion for replication tasks

When a replication task replicates source data, Data Integration converts the source datatype to a compatible datatype in the target database.
Data Integration converts datatypes to and from the following sources and targets:

Datatype conversion from Microsoft SQL Server 2000

The following table shows the conversion of Microsoft SQL Server 2000 datatypes to all target datatypes:
Microsoft SQL Server 2000 source
Flat file target
Microsoft SQL Server target
MySQL target
Oracle target
Bigint
Int
Bigint
Bigint
Number
Binary
Not supported.
Binary
Binary
Not supported.
Bit
Int
Int
Int
Number
Char
Nstring
Char
Char if precision is 255 or less. Text if precision is greater than 255.
Char
Cursor
Not supported.
Not supported.
Not supported.
Not supported.
Datetime
Datetime
Datetime
Datetime
Date
Decimal
Number
Decimal
Decimal
Number
Float
Number
Float
Double
Number
Image
Not supported.
Image
Longblob
Not supported.
Int
Int
Int
Int
Number
Money
Number
Decimal
Decimal
Number
Nchar
Nstring
Nchar
Char if precision is 255 or less. Text if precision is greater than 255.
Nchar
Ntext
Nstring
Ntext
Text
Nclob
Numeric
Number
Numeric
Double
Number
Nvarchar
Nstring
Nvarchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Nvarchar2
Real
Number
Real
Double
Number
Smalldatetime
Datetime
Datetime
Datetime
Date
Smallint
Int
Int
Int
Number
Smallmoney
Number
Decimal
Decimal
Number
Sql_variant
Nstring
Varchar(255)
Varchar(255)
Varchar2(255)
Table
Not supported.
Not supported.
Not supported.
Not supported.
Text
Nstring
Text
Text
Clob
Timestamp
Not supported.
Binary
Binary
Not supported.
Tinyint
Int
Tinyint
Int
Number
Uniqueidentifier
Nstring
Char(36)
Char(36)
Char(36)
Varbinary
Not supported.
Varbinary
Varbinary
Not supported.
Varchar
Nstring
Varchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Varchar

Datatype conversion from Microsoft SQL Server 2005

The following table shows the conversion of Microsoft SQL Server 2005 datatypes to all target datatypes:
Microsoft SQL Server 2005 source
Flat file target
Microsoft SQL Server target
MySQL target
Oracle target
Bigint
Int
Bigint
Bigint
Number
Binary
Not supported.
Binary
Binary
Not supported.
Bit
Int
Int
Int
Number
Char
Nstring
Char
Char if precision is 255 or less. Text if precision is greater than 255.
Char
Cursor
Not supported.
Not supported.
Not supported.
Not supported.
Datetime
Datetime
Datetime
Datetime
Date
Decimal
Number
Decimal
Decimal
Number
Float
Number
Float
Double
Number
Image
Not supported.
Image
Longblob
Not supported.
Int
Int
Int
Int
Number
Money
Number
Decimal
Decimal
Number
Nchar
Nstring
Nchar
Char if precision is 255 or less. Text if precision is greater than 255.
Nchar
Ntext
Nstring
Ntext
Text
Nclob
Numeric
Number
Numeric
Double
Number
Nvarchar
Nstring
Nvarchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Nvarchar2
Real
Number
Real
Float
Number
Smalldatetime
Datetime
Datetime
Datetime
Date
Smallint
Int
Int
Int
Number
Smallmoney
Number
Decimal
Decimal
Number
Sql_variant
Nstring
Varchar(255)
Varchar(255)
Varchar2(255)
Table
Not supported.
Not supported.
Not supported.
Not supported.
Text
Nstring
Text
Text
Clob
Timestamp
Not supported.
Binary
Binary
Not supported.
Tinyint
Int
Tinyint
Int
Number
Uniqueidentifier
Nstring
Char(36)
Char(36)
Char(36)
Varbinary
Not supported.
Varbinary
Varbinary
Not supported.
Varchar
Nstring
Varchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Varchar
Xml
Not supported.
Not supported.
Not supported.
Not supported.

Datatype conversion from Microsoft SQL Server 2008

The following table shows the conversion of Microsoft SQL Server 2008 datatypes to all target datatypes:
Microsoft SQL Server 2008 source
Flat file target
Microsoft SQL Server target
MySQL target
Oracle target
Bigint
Int
Bigint
Bigint
Number
Binary
Not supported.
Binary
Binary
Not supported.
Bit
Int
Int
Int
Number
Char
Nstring
Char
Char if precision is 255 or less. Text if precision is greater than 255.
Char
Cursor
Not supported.
Not supported.
Not supported.
Not supported.
Date
Not supported.
Not supported.
Not supported.
Not supported.
Datetime
Datetime
Datetime
Datetime
Date
Datetime2
Not supported.
Not supported.
Not supported.
Not supported.
Datetimeoffset
Not supported.
Not supported.
Not supported.
Not supported.
Decimal
Number
Decimal
Decimal
Number
Float
Number
Float
Double
Number
Image
Not supported.
Image
Longblob
Not supported.
Int
Int
Int
Int
Number
Money
Number
Decimal
Decimal
Number
Nchar
Nstring
Nchar
Char if precision is 255 or less. Text if precision is greater than 255.
Nchar
Ntext
Nstring
Ntext
Text
Nclob
Numeric
Number
Numeric
Double
Number
Nvarchar
Nstring
Nvarchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Nvarchar2
Real
Number
Real
Float
Number
Smalldatetime
Datetime
Datetime
Datetime
Date
Smallint
Int
Int
Int
Number
Smallmoney
Number
Decimal
Decimal
Number
Sql_variant
Nstring
Varchar(255)
Varchar(255)
Varchar2(255)
Table
Not supported.
Not supported.
Not supported.
Not supported.
Text
Nstring
Text
Text
Clob
Time
Not supported.
Not supported.
Not supported.
Not supported.
Timestamp
Not supported.
Binary
Binary
Not supported.
Tinyint
Int
Tinyint
Int
Number
Uniqueidentifier
Nstring
Char(36)
Char(36)
Char(36)
Varbinary
Not supported.
Varbinary
Varbinary
Not supported.
Varchar
Nstring
Varchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Varchar
Xml
Not supported.
Not supported.
Not supported.
Not supported.

Datatype conversion from MySQL

The following table shows the conversion of MySQL datatypes to all target datatypes:
MySQL source
Flat file target
Microsoft SQL Server target
MySQL target
Oracle target
Bigint
Int
Bigint
Bigint
Number
Binary
Not supported.
Binary
Binary
Not supported.
Bit
Not supported.
Not supported.
Not supported.
Not supported.
Blob
Not supported.
Image
Longblob
Not supported.
Bool
Int
Int
Int
Number
Boolean
Int
Int
Int
Number
Char
Nstring
Char (Nchar if MySQL code page is UTF-8)
Char
Char (Nchar if MySQL code page is UTF-8)
Date
Datetime
Not supported.
Date
Date
Datetime
Datetime
Datetime
Datetime
Date
Dec
Number
Decimal
Decimal
Number(p,s)
Decimal
Number
Decimal
Decimal
Number(p,s)
Double Precision
Number
Float
Double(p)
Number
Double(p,s)
Number
Decimal
Double(p,s)
Number(p,s)
Double[(p)]
Number
Float
Double(p)
Number
Enum
Nstring
Char (Nchar if MySQL code page is UTF-8)
Char
Char (Nchar if MySQL code page is UTF-8)
Float(p,s)
Number
Decimal
Double(p,s)
Number
Float[(p)]
Number
Real, Float
Float(p)
Number
Int
Int
Int
Int
Number
Integer
Int
Int
Int
Number
Longblob
Not supported.
Image
Longblob
Not supported.
Longtext
Nstring
Text (Ntext if MySQL code page is UTF-8)
Text
Clob (Nclob if MySQL code page is UTF-8)
Mediumblob
Not supported.
Image
Longblob
Not supported.
Mediumint
Int
Int
Int
Number
Mediumtext
Nstring
Text (Ntext if MySQL code page is UTF-8)
Text
Clob (Nclob if MySQL code page is UTF-8)
Serial
Int
Bigint
Bigint
Number
Set
Nstring
Char (Nchar if MySQL code page is UTF-8)
Char
Char (Nchar if MySQL code page is UTF-8)
Smallint
Int
Int
Int
Number
Text
Nstring
Text (Ntext if MySQL code page is UTF-8)
Text
Clob (Nclob if MySQL code page is UTF-8)
Time
Datetime
Not supported.
Time
Date*
Timestamp
Datetime
Datetime
Datetime
Date
Tinyblob
Not supported.
Binary
Binary
Not supported.
Tinyint
Int
Int
Int
Number
Tinytext
Nstring
Varchar (Nvarchar if MySQL code page is UTF-8)
Varchar
Varchar2 (Nvarchar if MySQL code page is UTF-8)
Varbinary
Not supported.
Varbinary
Varbinary
Not supported.
Varchar
Nstring
Varchar (Nvarchar if MySQL code page is UTF-8)
Varchar
Varchar2 (Nvarchar if MySQL code page is UTF-8)
Year
Not supported.
Not supported.
Not supported.
Not supported.
*The replication task converts MySQL time data to Oracle date data because Oracle does not contain a time datatype. Oracle date data contains the date and time. The task writes the current date and the time specified in the MySQL source to the Oracle date field.

Datatype conversion from Oracle

The following table shows the conversion of Oracle datatypes to all target datatypes:
Oracle source
Flat file target
Microsoft SQL Server target
MySQL target
Oracle target
Bfile
Not supported.
Not supported.
Not supported.
Not supported.
Binary_double
Number
Float
Double
Number
Blob
Not supported.
Image
Blob
Not supported.
Char
Nstring
Char
Char if precision is 255 or less. Text if precision is greater than 255.
Char
Clob
Nstring
Text
Text
Not supported.
Date
Datetime
Datetime
Datetime
Date
Dec
Number
Decimal
Decimal
Number
Decimal
Number
Decimal
Decimal
Number
Double Precision
Number
Float
Double
Number
Float
Number
Float
Double
Number
Int
Number
Decimal
Decimal
Number
Integer
Number
Decimal
Decimal
Number
Interval Day To Second
Not supported.
Not supported.
Not supported.
Not supported.
Interval Year To Month
Not supported.
Not supported.
Not supported.
Not supported.
Long
Nstring
Text
Text
Clob
Long Raw
Not supported.
Not supported.
Not supported.
Not supported.
Nchar
Nstring
Nchar
Char if precision is 255 or less. Text if precision is greater than 255.
Nchar
Nclob
Nstring
Ntext
Text
Not supported.
Number(p,s)
Number
Decimal
Decimal
Number
Number[(p)]
Number
Float
Float
Number
Numeric
Number
Float
Float
Number
Nvarchar2
Nstring
Nvarchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Nvarchar2
Raw
Not supported.
Varbinary
Varbinary
Not supported.
Real
Number
Float
Double
Number
Rowid
Nstring
Varchar(18)
Varchar(18)
Varchar2(18)
Smallint
Number
Decimal
Decimal
Number
Timestamp
Datetime
Datetime
Datetime
Date
Timestamp With Local Time Zone
Not supported.
Not supported.
Not supported.
Not supported.
Timestamp With Time Zone
Not supported.
Not supported.
Not supported.
Not supported.
Urowid
Not supported.
Not supported.
Not supported.
Not supported.
Varchar2
Nstring
Varchar
Varchar if precision is 255 or less. Text if precision is greater than 255.
Varchar2

Datatype conversion from Salesforce

The following sections describe datatype conversions from Salesforce to other target types.

Salesforce to Microsoft SQL Server datatypes

The following table shows the conversion of Salesforce datatypes to Microsoft SQL Server datatypes:
Salesforce datatype
Description
Microsoft SQL Server datatype
Description
AnyType
Values can be any of the following types: string, picklist, boolean, int, double, percent, ID, date, dateTime, url, or email.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Base64
Base64-encoded arbitrary binary data.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Boolean
Boolean values: True or False.
Int
-2,147,483,648 to 2,147,483,647.
Currency
Currency values.
Decimal
Precision is 1 to 38. Scale is 0 to 38.
DataCategoryGroupReference
Types of category groups and category unique names.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Date
Date values.
Datetime
Jan 1, 1753 AD to Dec 31, 9999 AD. Precision is 23. Scale is 3. (precision to 3.33 milliseconds)
Datetime
Date and time values.
Datetime
Jan 1, 1753 AD to Dec 31, 9999 AD. Precision is 23. Scale is 3. (precision to 3.33 milliseconds)
Double
Double values.
Decimal
Precision is 1 to 38. Scale is 0 to 38.
Email
Email addresses.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
ID
Primary key for a Salesforce object.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Int
Integer values.
Int
-2,147,483,648 to 2,147,483,647.
Multipicklist
Multiple-selection picklists, which provide a set of enumerated values that you can select multiple values from.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Percent
Percentage values.
Decimal
Precision is 1 to 38. Scale is 0 to 38.
Picklist
Single-selection picklists, which provide a set of enumerated values that you can select one value from.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Reference
Cross-reference to another Salesforce object.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
String
String values.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Textarea
String that appears as a multiple-line text field.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.
Time
Time values.
Datetime
Jan 1, 1753 AD to Dec 31, 9999 AD. Precision is 23. Scale is 3. (precision to 3.33 milliseconds)
Url
URL values.
Ntext if precision > 4000.
Ntext range is 1 to 1,073,741,823 characters.
Nvarchar if precision < 4000.
Nvarchar range is 1 to 4000 characters.

Salesforce to MySQL datatypes

You can load data from Salesforce fields of any datatype, except Text datatype with precision greater than 65535.
The following table shows the conversion of Salesforce datatypes to MySQL datatypes:
Salesforce datatype
Description
MySQL datatype
Description
AnyType
Values can be any of the following types: string, picklist, boolean, int, double, percent, ID, date, dateTime, url, or email.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Base64
Base64-encoded arbitrary binary data.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Boolean
Boolean (true/false) values.
integer
Precision is 11. Scale is 0. Range is 0 or 1.
Currency
Currency values.
decimal
Precision is 1 to 65. Scale is 1 to 30.
Date
Date values.
date
January 01, 1000 00:00:00 AD to December 31, 9999 23:59:59 AD. Precision is 19. Scale is 0. (precision to second. Partial seconds are truncated.)
DataCategoryGroupReference
Types of category groups and category unique names.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
DateTime
Date and time values.
timestamp
January 01, 1000 00:00:00 AD to December 31, 9999 23:59:59 AD.
Double
Double values.
decimal
Precision is 1 to 65. Scale is 1 to 30.
Email
Email addresses.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
ID
Primary key for a Salesforce object.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Int
Integer values.
integer
Precision is 11. Scale is 0. Range is -2147483648 to 2147483647.
Multipicklist
Multiple-selection picklists, which provide a set of enumerated values that you can select multiple values from.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Percent
Percentage values.
decimal
Precision is 1 to 65. Scale is 1 to 30.
Picklist
Single-selection picklists, which provide a set of enumerated values that you can select one value from.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Reference
Cross-reference to another Salesforce object.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
String
String values.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Textarea
String that appears as a multiple-line text field.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.
Time
Time values.
datetime
January 01, 1000 00:00:00 AD to December 31, 9999 23:59:59 AD. Precision is to second.
Url
URL values.
text if precision > 255.
text range is 1 to 65535 characters.
varchar if precision < 255.
varchar range is 1 to 255 characters.

Salesforce to Oracle datatypes

You can load data from Salesforce fields of any datatype, except binaries with precision greater than 2000.
The following table shows the conversion of Salesforce datatypes to Oracle datatypes:
Salesforce datatype
Description
Oracle datatype
Description
AnyType
Values can be any of the following types: string, picklist, boolean, int, double, percent, ID, date, dateTime, url, or email.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Base64
Base64-encoded arbitrary binary data.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Boolean
Boolean (true/false) values.
Number
Precision is 10. Scale is 0.
Currency
Currency values.
Number if scale equals 0.
Precision is 1 to 38.
Number(p,s) if scale is greater than 0.
Precision is 1 to 38. Scale is 0 to 38.
Date
Date values.
Date
Jan 1, 4712 BC to Dec 31, 4712 AD.
Precision is 19. Scale is 0.
DataCategoryGroupReference
Types of category groups and category unique names.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
DateTime
Date and time values.
Date
Jan 1, 4712 BC to Dec 31, 4712 AD.
Precision is 19. Scale is 0.
Double
Double values.
Number if scale equals 0.
Precision is 1 to 38.
Number(p,s) if scale is greater than 0.
Precision is 1 to 38. Scale is 0 to 38.
Email
Email addresses.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
ID
Primary key for a Salesforce object.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Int
Integer values.
Number
Precision is 10. Scale is 0.
Multipicklist
Multiple-selection picklists, which provide a set of enumerated values that you can select multiple values from.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Percent
Percentage values.
Number if scale equals 0.
Precision is 1 to 38.
Number(p,s) if scale is greater than 0.
Precision is 1 to 38. Scale is 0 to 38.
Picklist
Single-selection picklists, which provide a set of enumerated values that you can select one value from.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Reference
Cross-reference to another Salesforce object.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
String
String values.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Textarea
String that appears as a multiple-line text field.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.
Time
Time values.
Date
Jan 1, 4712 BC to Dec 31, 4712 AD.
Precision is 19. Scale is 0.
Url
URL values.
Nclob if precision > 2000.
Nclob range is up to 4 GB.
Nvarchar2 if precision < 2000.
Nvarchar2 range is 1 to 4000 bytes.