Data Transformation Services (DTS) Overview
From SQLServerPedia
|
See Also: Main_Page - Code Management - Loaders - Data Transformation Services (DTS) Data Transformation Services (DTS) is the most powerful set of interfaces for transferring data to and from SQL Server. You can use DTS to transfer data among other data sources, execute WIN32 processes, kick off data mining and cube processing tasks and other purposes. However, DTS has also simplified the data transfer and transform operations and has given us a programmable interface. DTS Component Object Model lets you program your own data transfer utilities; customize the transformations and error handling. The true power of DTS comes from the fact that it uses the OLE DB API to access a variety of data sources. With OLE DB you can write a single program to retrieve data from any type of data store: relational and non-relational databases, spreadsheets, text files, mail systems, LDAP-compliant directories and more. As long as the data source has an OLE DB service provider (or is ODBC compliant) you can use DTS to import the data from that source into SQL Server or any other OLE DB compliant store. One of the biggest advantages of using DTS is the ability to massage the data and shape it appropriately before loading it into the destination data store. If your data source is a non-relational database, you are guaranteed to have data inconsistencies. This means repeated data, missing values, values from the different columns appearing together in one column, etc. To put these data in cleaner relational format, you have to handle all of these issues. In fact, transformations are not limited to non-relational data sources. Another example could be moving data from relational to dimensional format when building a data warehouse (DW). When moving relational data into a warehouse, you may consider combining several columns to make reports easier to read. For instance, your relational database might store last name, first name and middle initial in separate columns. On the DW report, you may wish to show first name, middle initial and last name as one column. Also, instead of displaying just the store numbers on the reports, you might wish to show store number as well as the address, city and state where the store is located. With DTS you can perform data transformations behind the scenes, without any user input. You can perform these transformations depending on the values you find in a SQL Server table or other data store. DTS can also be used to easily transfer SQL Server objects other than user tables (stored procedures, user defined error messages, logins) to another instance of SQL Server. DTS transformations allow you to modify the data format while it is on its way from one data source to another. Types of transformations you can perform on the data are limited only by your imagination, however, this flexibility does come with a performance penalty. Since each row to be transformed has to be processed individually, DTS packages including transformations will perform significantly slower than their BCP or BULK INSERT counterparts not involving such transformations. Not only is DTS extremely powerful and extensible, its tools are also very easy to use. By answering a few questions of the data import / export wizard you can transfer huge amount of data within a few minutes. Within the wizard you can customize the data types you wish to use, table and column names, as well as data transformation. Finally, you can save your DTS application (referred to as package), run it on a scheduled basis, and even integrate it with your other applications. There are multiple ways to start the DTS import / export wizard. The various methods are listed below.
You can save DTS packages in SQL Server, within the SQL Server repository (referred to as Meta Data Services in SQL Server 2000), as structured storage files or as Visual Basic executables. When saving DTS packages within SQL Server or as structured storage files you have an option to provide user password, owner password or both. Users with the owner password can view, edit and execute the package. Users with the user password can only execute the package. To harness the true power of DTS, you need to go beyond the wizards and become familiar with its object model. The most important objects in the model are package, task, step, precedence constraint, transformation and connection. The DTS package object is really a container for other DTS objects. In fact, package cannot accomplish anything by itself; it has to contain steps and tasks. Tasks define specific chunks of work to be accomplished by the package, whereas steps determine the order of the task execution. Each package might contain multiple types of tasks and even multiple instances of the same task type. SQL Server 2000 comes with 19 built-in task types. In addition you can create your own custom task types and register them with SQL Server. Steps are used to control the flow of DTS package execution. The precedence constraint object is a condition, which has to occur before a particular step can be executed. A task within a step cannot be executed before all constraints in the step's Precedence Constraints collection have been satisfied. For example, you can define a precedence constraint that makes sure all the rows in the data source have been processed before sending a "success" email message to the database administrator. The Package object has to establish connections to the data sources and destinations in order to perform data transfer. The connection object is used to specify security credentials of the user querying a particular data store as well as type and location of the data store or files. The transformation object is where you can customize the data massaging operations before loading data into the destination data store. Full discussion of DTS capabilities is beyond the scope of this knowledge base. However, we do recommend that you familiarize yourself with DTS tools as well as its programming interfaces. The following table lists DTS built-in tasks available with SQL Server 7.0 and 2000:
For more detailed information on various aspects of DTS, click on each of the links below. |