DTS Data Conversion and Transformation Considerations

When preparing to use DTS to convert or transform data between heterogeneous data and destinations, some consideration should be given to variations in the way different programs, providers, and drivers support data types or SQL statements. Refer to this list for some important considerations.

SQL Server

• Transforming the Microsoft® SQL Server™ data type real into int may not return the exact value. For example, the real number 2147480000 may result in an int value of 2147480065. This is due to SQL Server supporting only six digits of precision for the real data type.
• When transforming from a string (DBTYPE_WSTR) into a date (DBTYPE_DATE) or time (DBTYPE_TIME) column from a text file, only one date or time format (yyyy-mm-dd hh:mm:ss.fffffffff) is accepted by the OLE DB data conversion service component. Use the CDate function to transform dates correctly.
• SQL Server does not support OLE DB types DBTYPE_DATE or DBTYPE_TIME.
• If you use a Microsoft Data Link when connecting the SQLOLEDB provider, you cannot use the IFastLoad interface.
• To access data across multiple steps, avoid using a temp table during transformations. Instead, use a global temp table or create a permanent table in tempdb.

DTS Import and DTS Export Wizards and DTS Designer

• The DTS user interface allows sharing existing connections among tasks but the same connection cannot be used for both the source and destination of a transformation.
• Using DTS Designer, it is possible to specify read-only or in-use status for some providers (for example, Access and ODBC DSNs) that are to serve as data sources only. Click the Advanced tab in the Connection Properties dialog box, and set the value of the mode property to 1.
• When creating a table using the DTS Import and DTS Export wizards or DTS Designer, the owner of a table created at the destination is the current user (generally the dbo), regardless of who the owner is at the source. This can result in a situation where the dbo at the source subsequently attempts to create a table at the destination and the table name already exists, thus causing the attempt to fail.
• When defining a data-driven query using the DTS Designer, a data destination must be able to support the OLE DB ICommand interface. Due to this restriction, destinations such as text files are not supported.

Microsoft SNA Server

• The Microsoft OLE DB provider for AS/400 and VSAM does not support SQL statements that DTS Import and DTS Export wizards use to create or truncate a table.

Microsoft Access

• When exporting data from SQL Server to Microsoft Access 97 or earlier, the Microsoft OLE DB Provider for Access buffers all inserts in memory, and only commits them when the DTS Export Wizard completes operation. This can result in a low memory situation when exporting large tables, but can be resolved by constructing SELECT statements that send smaller numbers of rows in multiple passes.

Microsoft Visual FoxPro

• Microsoft Visual FoxPro® supports only a precision of (15,9) for numeric data types. Data exported to Microsoft Fox Pro that exceeds this precision is truncated and rounded.
• Visual FoxPro does not support the SELECT INTO statement.
• The DTS Query Builder supports the Visual FoxPro INSERT VALUE statement, but not the INSERT statement using a SELECT statement.
• The Microsoft OLE DB driver for ODBC is unable to write BLOBs to Visual FoxPro using the FoxPro ODBC driver because Visual FoxPro does not support dynamic cursors.

ODBC

• The Microsoft OLE DB Provider for ODBC requires 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. You can use a SELECT statement to rearrange the BLOB columns to the end of the source rowset. The DTS Wizard performs this operation automatically.

Oracle

• The Microsoft ODBC and OLE DB drivers for Oracle support the Oracle 7.3 BLOB data types, not Oracle 8.0 data types (for example, BLOB, CLOB, NCLOB, and BFILE are not supported).
• The Microsoft OBDC driver for Oracle does not support sending Unicode strings into an Oracle server. Oracle requires prefixing Unicode strings with the letter N.
• The Microsoft OBDC driver for Oracle does not support negative scaling for the Oracle number data type.
• The Microsoft OBDC driver for Oracle reports that an Oracle number data type without a specified precision has a size of 20 digits. When importing from Oracle (regardless of the destination), if there are more than 20 digits, you may have to manually increase the precision if the destination table does not already exist.
• 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 also cannot transform or copy data using Oracle column names that contain spaces using the DTS Import and DTS Export wizards. Oracle requires case-sensitive column names to be precisely specified and quoted.
• To perform distributed transactions between SQL Server and Oracle, you must use Oracle version 8.0.4.1 or later. For information about distributed transactions, see MS DTC Service.

DB2 on the IBM AS/400

• There is no Unicode or BLOB support on the AS/400®.
• You cannot transform any table with a NULL column value to an AS/400 server because the AS/400 does not support NULL syntax in its CREATE TABLE statement. However, you can send NULLs if you edit the CREATE TABLE syntax to remove the references to NULL. The AS/400 does not support NOT NULL; NULL is assumed if not specified.

Using the Sybase ODBC Driver

• When transforming data from SQL Server into Sybase version 11 using the DTS Export Wizard:
  • The SQL Server numeric (3,0) data type maps to the Sybase smallmoney data type by default. Change this setting to avoid data loss.
  • The SQL Server numeric (18,x or 19,x) data type maps to the Sybase money data type by default. Change this setting to avoid data loss.
  • 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 wizards. You must perform this action without using a wizard.
• The DTS Query Builder does not support the Sybase SQLAnywhere CREATE TABLE statement.
• The DTS Export Wizard can only move one table at a time to a SQLAnywhere database due to a limitation in the SQLAnywhere driver. You can overcome this limitation using DTS Designer. However, you must set the Step object’s ExecuteInMainThread property to True for each table.
• You cannot copy a table to a Sybase destination if it contains a BLOB column.
• If you programmatically copy a table containing an image data type from Sybase, changing the default BLOB settings can result in failure.

dBase and Paradox

• Table names in dBase and Paradox are limited to eight characters. Column names in dBase are limited to 10 characters.

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.