Transforming data with Microsoft SQL Server 2000 DTS

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 InformIT.

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.

DTS Packages

A DTS package is a collection of programmable objects that work together to accomplish a specific goal. You can use a DT...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Management and Administration Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication Protect virtual databases through SQL Server database mirroring

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

S 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.