Consolidating disparate data formats into single or multiple databases can be trying, especially on deadline. Microsoft SQL Server 2000 includes some tools that are specially designed to aid in the collection of data while insuring data integrity. These tools are called ETLs and one of them, DTS, is described in this tip from
Microsoft SQL Server 2000 includes a very powerful and flexible Extraction Transformation Loading (ETL) tool: Data Transformation Services (DTS). With DTS, Microsoft gives SQL Server 2000 the power to import, export, and transform data from disparate sources into single or multiple destinations. Microsoft groups all these elements in a DTS package.
To create and manage DTS packages, SQL Server 2000 includes several tools:
- The DTS Import/Export Wizard creates simple DTS packages.
- The DTS Designer is a graphical application for creating and editing DTS packages.
- The Data Transformation Services node in the SQL Server Enterprise Manager console tree is used to view, create, load, execute, and manage DTS packages.
- Package execution utilities are command-line utilities for managing DTS packages.
- The DTS Query Designer is a graphical tool for creating SQL queries in DTS Designer.
A DTS package is a collection of programmable objects that work together to accomplish a specific goal. You can use a DTS package to perform a variety of tasks:
- Import and export data. A DTS package can import or export data to any OLE database or ODBC-compliant data source. Microsoft SQL Server 2000 includes drivers to connect to SQL Server, Oracle, Access, Excel, Visual FoxPro, dBASE, Paradox, HTML, text files, Microsoft Exchange server, Microsoft Active Directory, and Microsoft Analysis Services.
- Transform data. Execute ActiveX scripts or scripts written in JScript, VBScript, or PerlScript.
- Copy SQL Server objects. Copy data, indexes, triggers, and so on among two or more SQL Server databases.
- Send and receive messages. Send messages from a DTS package to users or to other DTS packages.
- Execute T-SQL commands. Automate the execution of T-SQL commands with DTS packages.
A DTS package contains a series of steps (tasks) that the package must complete. These tasks can be executed in sequence or parallel with one another. For example, if you want to copy data between two tables, you must first check that the destination table exists before copying data. If the table doesn't exist, the DTS package must create it before copying the data to it. The DTS package must execute these steps in sequence. If you want to copy data among several tables, you can run several parallel sequences to save time.
See examples of DTS package usage at InformIT.com.
This was first published in March 2005