DTS vs SSIS
Data Transformation Services (also known as DTS) was the predecessor to the SSIS system. It is a set of objects using an ETS tool (which means a tool that extracts, transforms, and loads information into a database for warehousing) in order to extract, transform, and load this information to and/or from a database.
SQL Server Integration Services (also known as SSIS) is an ETL tool that Microsoft provides to its users in order to extract data from different sources. It then transforms the said data according to the requirements by individual businesses, and loads it into that particular destination (hence ETL).
DTS was an original component of the Microsoft SQL Server 2000, and at its inception, was always used with the SQL Server databases. Even though it was an integral part of the server, DTS was also easily used independently from the Microsoft server, in conjunction with other databases. It is capable of transforming and loading data from heterogeneous sources, using OLE DB, ODBC, or files specified as text only, into any database that supports them.
SSIS is a component of the Microsoft SQL Server 2005. As such, SSIS does not require a separate installation. It can be used for anything that allows the user to communicate through an active connection. It includes graphical tools and wizards in order to build and debug packages, all tasks that are used to perform workflow functions for several different operations (such as FTP operations), execute SQL statements, or send email. There are also data sources that are used to extract and load data, and transformations that used to clean, aggregate, merge, and copy data.
DTS packages are implemented whenever data is modified using the DTS. These can be saved directly on the SQL Server, or they can be saved in COM files (also known as the Microsoft Repository). As a part of the 2000 version of the SQL Server, programmers were allowed to save packages in a Visual Basic language file – unless, of course, they found another language file more sufficient. When saved as a VB file, the package is scripted in order to create the objects and component objects found in the package.
Summary:
1. DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database; SSIS is an ETL tool provided by Microsoft to extra data from different sources.
2. DTS was originally part of the Microsoft SQL Server 2000; SSIS is a component of the Microsoft SQL Server 2005.