Data Transformation Services

DTS allows data to be transformed and loaded from heterogeneous sources using OLE DB, ODBC, or text-only 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.

With the release of SQL Server 7 in 1998, "Data Transformation Services" was packaged with it to replace all these tools.

Microsoft also integrated packages into Windows 2000 security and made DTS tools more user-friendly; tasks can accept input and output parameters.

For example, the following types of tasks represent some actions that you can perform by using DTS: executing a single SQL statement, sending an email, and transferring a file with FTP.

SQL Server 2000 allows input and output parameters for tasks, greatly expanding the usefulness of global variables.

DTS packages can be edited, password protected, scheduled for execution, and retrieved by version.

These icons are joined with precedence constraints, which specify the order and requirements for tasks to be run.

The DTS Designer has been criticized for having unusual quirks and limitations, such as the inability to visually copy and paste multiple tasks at one time.

DTS Packages can be run from the command line using the DTSRUN Utility.

The utility is invoked using the following syntax: When passing in parameters which are mapped to Global Variables, you are required to include the typeid.