Microsoft SQL Server Source and Microsoft SQL Server Target
The following table identifies the recommended data-type mappings for Database Ingestion and Replication configurations with a Microsoft SQL Server source and a Microsoft SQL Server target:
SQL Server Source Data Type
SQL Server Target Data Type
bigint
bigint
binary
varbinary
bit
bit
char
varchar
date
date
datetime
datetime
datetime2
datetime2
datetimeoffset
datetimeoffset
decimal
decimal
float
float
geography
geography
geometry
geometry
hierarchyid
hierarchyid
image
image
int
int
money
money
nchar
nvarchar
ntext
ntext
numeric
numeric
nvarchar
nvarchar
real
real
smalldatetime
smalldatetime
smallint
smallint
smallmoney
smallmoney
sql_variant
sql_variant
text
text
time
time
timestamp
varbinary
tinyint
tinyint
uniqueidentifier
uniqueidentifier
varbinary
varbinary
varchar
varchar
xml
xml
Sql_variant target data type
Database ingestion and replication initial load jobs that have a SQL Server source and a SQL Server target and include a sql_variant source column convert the sql_variant data to hexadecimal format on the target. To convert data from hexadecimal format to varbinary format, run the following query:
SELECT <column_name>, CONVERT(varbinary,<column_name>) from <table_name>;
Replace <column_name> and <table_name> with the actual target column and table names.
LOB limitations
Database ingestion and replication initial load and incremental load jobs can replicate data from SQL Server GEOGRAPHY, GEOMETRY, IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), VARCHAR(MAX), and XML columns to SQL Server targets. LOB data might be truncated before being written to the target. The truncation point depends on the data type and target type. For initial load jobs with a SQL Server target, GEOGRAPHY, GEOMETRY, IMAGE, TEXT, VARBINARY(MAX), and VARCHAR(MAX) data is truncated to 16777216 bytes, NTEXT and NVARCHAR(MAX) data to 33554432 bytes , and XML data to 33554442 bytes.