Data Transformation Server Services in SQL Server

Data Transformation Services (DTS) is a utility that can transfer data between any OLE-DB data source.
While transferring the data, DTS can also transform the data. Depending on your data sources and your OLE-DB drivers you will have different limitations on that data being transferred.

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.

When exporting data to dBase and Paradox table names are limited to eight characters.
Column names in dBase are limited to 10 characters.

You have the following limitations when exporting to DB2 on the AS/400

1. There is no Unicode or BLOB support
   
2. 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.

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.

1. Only Intersolves ODBC driver for Informix is supported. SQL Server does not support Informix’s ODBC driver when you are using DTS.
2. BLOBS can not be exported to Informix
3. DTS Wizard will incorrectly assign the Informix datatype ‘datetime to fraction’ to SQL Server datetime datatype
4. DTS will not important Informix catalog information

You have the following limitations when an accessing a JET (access) data source.

1. DTS is not supported with the JET version 3.5.1 or earlier
2. 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

1. You need a unique key on all destination tables with a BLOB data column when performing export operations.
2. 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

1. You can use the Oracle 7.3 BLOB data types, but not Oracle 8.0 data types
2. You can not export Unicode strings into an Oracle server.
3. You can not use negative scaling for the Oracle number data type.
4. 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

1. Oracle supports only one LONG (BLOB) data column in a table.
2. 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

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

1. 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.
   
2. SQL Server will only import a string date or time format into a SQL Server datetime datatype if the string looks like ‘yyyy-mm-dd hh:mm:;ss.fffffffff

You have the following issues when you use the Sybase ODBC driver

1. The SQL Server numeric (3,0) data type maps to the Sybase smallmoney data type.
2. The SQL Server numeric (18,x or 19,x) data type maps to the Sybase money data type.
3. 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.
4. You cannot drop and re-create a Sybase table using the DTS Import and DTS Export wizard.
5. The DTS Query Builder does not support the Sybase SQLAnywhere CREATE TABLE statement.
6. You cannot copy a table to a Sybase destination if it contains a BLOB column

(DTS) Data Transformation Services is a set of objects and utilities to allow the

1. automation of extract,
2. transform and
3. load operations

to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases.
DTS allows data to be transformed and loaded from heterogeneous sources using OLE DB, ODBC, or text files, into any supported database. DTS can also allow automation of data import or transformation on a scheduled basis, and can perform additional functions such as FTPing files and executing external programs.
Furthermore, DTS provides an alternative method of version control and backup for packages when used in conjunction with a version control system, such as Microsoft Visual SourceSafe.
DTS has been superseded by SQL Server Integration Services in later releases of Microsoft SQL Server though there was some backwards compatibility and ability to run DTS packages in the new SSIS for a time.

DTS’s strong point is its ability to perform multiple transformations on data. When transforming data, you can manipulate the source data before it is stored in the destination. This allows you to break complex transformations into multiple steps in order to better manage the transformation process. You can transform multiple data sources independently of one another, combining the results in a final step. On the other hand, DTS can break one record up to multiple destinations.

DTS can move both the schema and data between the source and destination data sources, however, triggers, stored procedures, rules, defaults, constraints, and user-defined data types are not transfered between data sources.
The next lesson will explain how to use the DTS Designer to make a package.