Data Transformation Services
Encyclopedia
Data Transformation Services, or DTS, is a set of objects and utilities to allow the automation of extract, transform and 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-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. In addition, 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
.
, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools such as Pervasive Data Integrator to transfer data more flexibly and easily. When SQL Server 7 was released, "Data Transformation Services" was packaged with it to replace all these tools.
SQL Server 2000 expanded DTS functionality in several ways. Many new types of tasks were made, including the ability to FTP
files, move databases or database components, and add messages into Microsoft Message Queue
. DTS packages can be saved as a Visual Basic file in SQL Server 2000, and this can be expanded to save into any COM-compliant language. Packages were also integrated into Windows 2000 security, DTS tools were made more user-friendly, and tasks can accept input and output parameters.
DTS comes with all editions of SQL Server 7 and 2000, but was superseded by SQL Server Integration Services
in the Microsoft SQL Server 2005 release.
of the package. Packages are used whenever one modifies data using DTS. All the metadata
about the data transformation is contained within the package. Packages can be saved directly in a SQL Server, or can be saved in the Microsoft Repository or in COM
files. SQL Server 2000 also allows a programmer to save packages in a Visual Basic
or other language file. (When stored to a VB file, the package is actually scripted -- that is, a VB script is executed to dynamically create the package objects and its component objects.)
A package can contain any number of connection objects
, but does not have to contain any. These allow the package to read data from any OLE DB
-compliant data source, and can be expanded to handle other sorts of data. The functionality of a package is organized into tasks and steps.
A DTS Task is a discrete set of functionalities executed as a single step in a DTS package. Each task defines a work item to be performed as part of the data movement and data transformation process or as a job to be executed.
Data Transformation Services supplies a number of tasks that are part of the DTS object model
and that can be accessed graphically through the DTS Designer or accessed programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation and notification situations. 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.
A step within a DTS package describes the order in which tasks are run and the precedence constraints that describe what to do in the case of failure. These steps can be executed sequentially or in parallel.
Packages can also contain global variable
s which can be used throughout the package. 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.
can be used to perform simple or common DTS tasks. These include the Import/Export Wizard and the Copy Database Wizard. They provide the simplest method of copying data between OLE DB
data sources. There is a great deal of functionality that is not available by merely using a wizard. However, a package created with a wizard can be saved and later altered with one of the other DTS tools.
A Create Publishing Wizard is also available to schedule packages to run at certain times. This only works if SQL Server Agent
is running; otherwise the package will be scheduled, but will not be executed.
Each connection and task in DTS Designer is shown with a specific icon
. These icons are joined with precedence constraints, which specify the order and requirements for tasks to be run. One task may run, for instance, only if another task succeeds (or fails). Other tasks may run concurrently.
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. Many of these shortcomings have been overcome in SQL Server Integration Services
, DTS's successor.
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. This is rather difficult to find on the Microsoft site. Below are the TypeIds used in passing in these values.
Extract, transform, load
Extract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...
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
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
, 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
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
, ODBC, or text-only files, into any supported database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
. DTS can also allow automation of data import or transformation on a scheduled basis, and can perform additional functions such as FTPing
File Transfer Protocol
File Transfer Protocol is a standard network protocol used to transfer files from one host to another host over a TCP-based network, such as the Internet. FTP is built on a client-server architecture and utilizes separate control and data connections between the client and server...
files and executing external programs. In addition, 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
Microsoft Visual SourceSafe
Microsoft Visual SourceSafe is a source control software package oriented towards small software development projects. Like most source control systems, SourceSafe creates a virtual library of computer files...
.
History
In SQL Server versions 6.5 and earlier, Database administrators (DBAs) used SQL Server Transfer Manager and Bulk Copy ProgramBulk Copy Program
Bulk Copy Program is a command-line tool used to import or export data against a Microsoft SQL Server or Sybase database.The tool is often more efficient than more recent GUI-based applications, such as DTS, to import and extract data.-Common Uses:...
, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools such as Pervasive Data Integrator to transfer data more flexibly and easily. When SQL Server 7 was released, "Data Transformation Services" was packaged with it to replace all these tools.
SQL Server 2000 expanded DTS functionality in several ways. Many new types of tasks were made, including the ability to FTP
File Transfer Protocol
File Transfer Protocol is a standard network protocol used to transfer files from one host to another host over a TCP-based network, such as the Internet. FTP is built on a client-server architecture and utilizes separate control and data connections between the client and server...
files, move databases or database components, and add messages into Microsoft Message Queue
Microsoft Message Queuing
Microsoft Message Queuing or MSMQ is a Message Queue implementation developed by Microsoft and deployed in its Windows Server operating systems since Windows NT 4 and Windows 95. The latest Windows 7 also includes this component...
. DTS packages can be saved as a Visual Basic file in SQL Server 2000, and this can be expanded to save into any COM-compliant language. Packages were also integrated into Windows 2000 security, DTS tools were made more user-friendly, and tasks can accept input and output parameters.
DTS comes with all editions of SQL Server 7 and 2000, but was superseded by SQL Server Integration Services
SQL Server Integration Services
SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks....
in the Microsoft SQL Server 2005 release.
DTS packages
The DTS package is the fundamental logical component of DTS; every DTS object is a child componentInformation hiding
In computer science, information hiding is the principle of segregation of the design decisions in a computer program that are most likely to change, thus protecting other parts of the program from extensive modification if the design decision is changed...
of the package. Packages are used whenever one modifies data using DTS. All the metadata
Metadata
The term metadata is an ambiguous term which is used for two fundamentally different concepts . Although the expression "data about data" is often used, it does not apply to both in the same way. Structural metadata, the design and specification of data structures, cannot be about data, because at...
about the data transformation is contained within the package. Packages can be saved directly in a SQL Server, or can be saved in the Microsoft Repository or in COM
Component Object Model
Component Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
files. SQL Server 2000 also allows a programmer to save packages in a Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
or other language file. (When stored to a VB file, the package is actually scripted -- that is, a VB script is executed to dynamically create the package objects and its component objects.)
A package can contain any number of connection objects
ActiveX Data Objects
Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
, but does not have to contain any. These allow the package to read data from any OLE DB
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
-compliant data source, and can be expanded to handle other sorts of data. The functionality of a package is organized into tasks and steps.
A DTS Task is a discrete set of functionalities executed as a single step in a DTS package. Each task defines a work item to be performed as part of the data movement and data transformation process or as a job to be executed.
Data Transformation Services supplies a number of tasks that are part of the DTS object model
Object model
In computing, object model has two related but distinct meanings:# The properties of objects in general in a specific computer programming language, technology, notation or methodology that uses them. For example, the Java objects model, the COM object model, or the object model of OMT...
and that can be accessed graphically through the DTS Designer or accessed programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation and notification situations. 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.
A step within a DTS package describes the order in which tasks are run and the precedence constraints that describe what to do in the case of failure. These steps can be executed sequentially or in parallel.
Packages can also contain global variable
Global variable
In computer programming, a global variable is a variable that is accessible in every scope . Interaction mechanisms with global variables are called global environment mechanisms...
s which can be used throughout the package. 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.
DTS tools
DTS tools packaged with SQL Server include the DTS wizards, DTS Designer, and DTS Programming Interfaces.DTS wizards
The DTS wizardsWizard (software)
A software wizard or setup assistant is a user interface type that presents a user with a sequence of dialog boxes that lead the user through a series of well-defined steps. Tasks that are complex, infrequently performed, or unfamiliar may be easier to perform using a wizard...
can be used to perform simple or common DTS tasks. These include the Import/Export Wizard and the Copy Database Wizard. They provide the simplest method of copying data between OLE DB
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
data sources. There is a great deal of functionality that is not available by merely using a wizard. However, a package created with a wizard can be saved and later altered with one of the other DTS tools.
A Create Publishing Wizard is also available to schedule packages to run at certain times. This only works if SQL Server Agent
SQL Server Agent
SQL Server Agent is a process which executes SQL jobs and handles other automated tasks. It can be configured to run automatically when the system boots or it can be started manually. It is a component of Microsoft SQL Server....
is running; otherwise the package will be scheduled, but will not be executed.
DTS Designer
The DTS Designer is a graphical tool used to build complex DTS Packages with workflows and event-driven logic. DTS Designer can also be used to edit and customize DTS Packages created with the DTS wizard.Each connection and task in DTS Designer is shown with a specific icon
Icon (computing)
A computer icon is a pictogram displayed on a computer screen and used to navigate a computer system or mobile device. The icon itself is a small picture or symbol serving as a quick, intuitive representation of a software tool, function or a data file accessible on the system. It functions as an...
. These icons are joined with precedence constraints, which specify the order and requirements for tasks to be run. One task may run, for instance, only if another task succeeds (or fails). Other tasks may run concurrently.
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. Many of these shortcomings have been overcome in SQL Server Integration Services
SQL Server Integration Services
SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks....
, DTS's successor.
DTS Run Utility
DTS Packages can be run from the command line using the DTSRUN Utility.The utility is invoked using the following syntax:
dtsrun /S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
When passing in parameters which are mapped to Global Variables, you are required to include the typeid. This is rather difficult to find on the Microsoft site. Below are the TypeIds used in passing in these values.
| Type | typeid |
|---|---|
| Boolean | 11 |
| Currency | 6 |
| Date | 7 |
| Decimal | 14 |
| HRESULT | 25 |
| Int | 22 |
| Integer (1-byte) | 16 |
| Integer (8-byte) | 20 |
| Integer (small) | 2 |
| Integer | 3 |
| LPSTR | 30 |
| LPWSTR | 31 |
| Pointer | 26 |
| Real (4-byte) | 4 |
| Real (8-byte) | 5 |
| String | 8 |
| Unsigned int (1-byte) | 17 |
| Unsigned int (2-byte) | 18 |
| Unsigned int (4-byte) | 19 |
| Unsigned int (1-byte) | 21 |
| Unsigned int | 23 |
See also
- OLAPOLAPIn computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
- Data warehouseData warehouseIn computing, a data warehouse is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.A data warehouse...
- Data miningData miningData mining , a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at the intersection of artificial intelligence, machine learning, statistics and database systems...
- SQL Server Integration ServicesSQL Server Integration ServicesSQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks....
- Meta Data ServicesMeta Data ServicesMeta Data Services is an object-oriented repository technology that can be integrated with enterprise information systems or with applications that process metadata....
.
External links
- Microsoft SQL Server: Data Transformation Services (DTS)
- SQL DTS unique DTS information resource
- Understanding Microsoft Repository
- DTS Videos & Training