DTS Limitations of SQL Server
When using DTS you have various limitations on the data that can be imported and exported. These limitations are based upon the vendor of the
data source and of the OLE-DB or ODBC driver. These limitations can change at any time without any changes in DTS or SQL Server.
dBase and Paradox
When exporting data to dBase and Paradox table names are limited to eight characters.
Column names in dBase are limited to 10 characters.
DB2 on the IBM AS/400
You have the following limitations when exporting to DB2 on the AS/400
There is no Unicode or BLOB support
You cannot transform any table with a NULL column value to an AS/400 server without editing the CREATE TABLE syntax to remove the
references to NULL.
File Import or Export
If you import into or export from char or varchar columns, some extended characters may not be copied correctly if your client OEM code page
is different from the code page on the server.
When you import into or export from nchar or nvarchar columns, all characters copy correctly.
You have the following limitations when using Informix as a data source with DTS.
- Only Intersolves ODBC driver for Informix is supported. SQL Server does not support Informix’s ODBC driver when you are using DTS.
- BLOBS can not be exported to Informix
- DTS Wizard will incorrectly assign the Informix datatype ‘datetime to fraction’ to SQL Server datetime datatype
- DTS will not important Informix catalog information
Jet data source
You have the following limitations when an accessing a JET (access) data source.
- DTS is not supported with the JET version 3.5.1 or earlier
When exporting from Access 97, DTS loads all of the data into memory
When using the Microsoft OLE DB Provider for ODBC you have the following limitations
You need a unique key on all destination tables with a BLOB data column when performing export operations.
When using the Microsoft OLE DB provider for ODBC with the SQL Server ODBC driver, all BLOB columns should be arranged after columns
with other data types in a source rowset.
When using the Microsoft ODBC and OLE DB drivers for Oracle you have these limitations
- You can use the Oracle 7.3 BLOB data types, but not Oracle 8.0 data types
- You can not export Unicode strings into an Oracle server.
- You can not use negative scaling for the Oracle number data type.
- When exporting Oracle numeric data, If there are more than 20 digits, you may have to manually increase the precision when you are creating the destination table .
You have the following Oracle limitations
Oracle supports only one LONG (BLOB) data column in a table.
You cannot import or export Oracle columns that have mixed or lower case names. You cannot transform or copy data using Oracle column
names that contain spaces
SNA data sources
When accessing a SNA data source you can not truncate or create an AS/400 or VSAM table
You have the following limitations when using SQL Server as a data source with DTS
Since SQL Server real datatype is not an exact datatype, a real converting to an integer may result in a different number stored in the
integer data type.
SQL Server will only import a string date or time format into a SQL Server datetime datatype if the string looks like
Sybase ODBC Driver
You have the following issues when you use the Sybase ODBC driver
- The SQL Server numeric (3,0) data type maps to the Sybase smallmoney data type.
- The SQL Server numeric (18,x or 19,x) data type maps to the Sybase money data type.
- When moving data into a new Sybase table, if you click OK in the Column Mappings and Transformations dialog box, the wizard returns a “Table already exists” error message. You should ignore this message.
- You cannot drop and re-create a Sybase table using the DTS Import and DTS Export wizard.
- The DTS Query Builder does not support the Sybase SQLAnywhere CREATE TABLE statement.
- You cannot copy a table to a Sybase destination if it contains a BLOB column