|
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 is included with Microsoft SQL Server, and is almost always used with SQL Server databases, although it can be used independently with other databases. ETL also means Express Toll Lanes, see Express Toll Lanes. ...
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. ...
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 . Look up Heterogeneous in Wiktionary, the free dictionary. ...
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing different types of data stores in a uniform manner. ...
Open Database Connectivity (ODBC) is a standard software API for connecting to database management systems (DBMS). ...
This article is about computing. ...
This article is about the File Transfer Protocol standardised by the IETF. For other file transfer protocols, see File transfer protocol (disambiguation). ...
Microsoft Visual SourceSafe (VSS) is a source control software package oriented towards smaller software development projects. ...
 Here a DTS package is edited with DTS Designer in Windows XP. Image File history File links Download high resolution version (811x357, 29 KB) Summary This image was created by me, User:Quadell, and I claim no rights to it. ...
Image File history File links Download high resolution version (811x357, 29 KB) Summary This image was created by me, User:Quadell, and I claim no rights to it. ...
Windows XP is a line of operating systems developed by Microsoft for use on general-purpose computer systems, including home and business desktops, notebook computers, and media centers. ...
History
In SQL Server versions 6.5 and earlier, Database administrators (DBAs) used SQL Server Transfer Manager and Bulk Copy Program, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools such as Data Junction 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. A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. ...
A Data Junction by Data Junction Corp. ...
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. This article is about the File Transfer Protocol standardised by the IETF. For other file transfer protocols, see File transfer protocol (disambiguation). ...
Microsoft Message Queuing or MSMQ is a technology developed by Microsoft and deployed in its Windows Server operating systems since Windows NT 4 and Windows 95. ...
...
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. SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. ...
DTS packages The DTS package is the fundamental logical component of DTS; every DTS object is a child component 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.) In computer science, the principle of information hiding is the hiding of design decisions in a computer program that are most likely to change, thus protecting other parts of the program from change if the design decision is changed. ...
Metadata is data about data. ...
Component Object Model (COM) is a platform for software componentry introduced by Microsoft in 1993. ...
This article is about the Visual Basic language shipping with Microsoft Visual Studio 6. ...
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. // Microsoft ActiveX Data Objects (ADO) is a set of Component Object Model objects for accessing data sources. ...
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing different types of data stores in a uniform manner. ...
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. In the computing discipline object model has two related but distinct meanings: 1. ...
In SQL Server database application design and development DTS = Data Transformation Services. ...
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 variables 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. In computer programming, a global variable is a variable that is accessible in every scope. ...
DTS tools using DTS tools, included with SQL Server. These tools are the DTS wizards, DTS Designer, and DTS Programming Interfaces.
DTS wizards The DTS wizards 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 wizard is an interactive computer program acting as an interface to lead a user through a complex task using dialog steps. ...
OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing different types of data stores in a uniform manner. ...
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.
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. It has been suggested that Look and feel be merged into this article or section. ...
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. Nuvola icons for KDE are available as PNG images, which come in six sizes, and SVG images, which are scalable On computer displays, a computer icon is a small pictogram. ...
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. This page is about computer text editing. ...
SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. ...
DTS Package execution utilities DTS Package execution utilities include the following: - The dtswiz command prompt utility enables you to start the DTS Import/Export Wizard from a command prompt.
The dtswiz command includes a set of command switches. Command Prompt on Windows XP. A command prompt (or just prompt) is a character or string of characters used in a command line interface to indicate that the computer is ready to accept typed input. ...
In computer software, specifically command line interfaces, a switch (also known as option, command-line parameter, or command-line argument) is an indication by a user that a computer program should change its default behaviour. ...
- The dtsrun command prompt utility enables you to execute an existing DTS Package from a command prompt.
The dtsrun command includes a set of command switches. - The DTS Run utility provides a set of dialog boxes that are used to execute an existing DTS Package. You can run the DTS Run utility by executing dtsrunui from a command prompt without any command switches.
- The dtsrunui utility enables you to generate DTSRun commands that can be run from the command line using a GUI. Very easy to use and faster generation of commands that require multiple parameters.
Example of dialog box from Microsoft Windows Dialog boxes are special windows which are used by computer programs or by the operating system to display information to the user, or to get a response if needed. ...
DTS Query Designer A graphical tool used to build queries in DTS. In general, a query is a form of questioning, in a line of inquiry. ...
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.
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 (8-byte) 21 Unsigned int 23
See also On Line Analytical Processing, or OLAP, is an approach to quickly provide answers to analytical queries that are dimensional in nature. ...
A data warehouse is the main repository of an organizations historical data, its corporate memory. ...
Data mining is the principle of sorting through large amounts of data and picking out relevant information. ...
SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. ...
There are very few or no other articles that link to this one. ...
References - Chaffin, Mark; Knight, Brian; and Robinson, Todd (2003). Professional SQL Server 2000 DTS. Wrox Press (Wiley Publishing, Inc.). ISBN 0-7645-4368-7.
One of Wrox books Wrox press (established in 1992) is a computer book publisher, based in the UK. Wrox has pioneered the philosophy of Programmer to Programmer™ books for technology professionals. ...
External links | 
Feeds |
Contact