SQL Server Integration Services

SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. It replaces Data Transformation Services, which has been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is available in the "Standard", "Professional" and "Enterprise" editions.

Integration Services provides a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing, as the product features a fast and flexible tool for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases, update multidimensional cube data, and perform other functions.

FeaturesSSIS features a wizard that lets the user readily create a package which moves data from a single data source to a destination with no transformations. This tool is called the Import/Export Wizard. The Wizard is appropriate for use to quickly move data into or out of SQL Server from or to a variety of sources, including text files and other SQL Server instances.

Developers tasked with creating or maintaining SSIS packages utilize a visual development tool based on Microsoft Visual Studio called the SQL Server Business Intelligence Studio. It allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment in which to write programming code is also available in the tool. The package holds a variety of elements that define the workflow for the package. Upon package execution, the tool provides color-coded, real-time monitoring.

Connections A connection includes the information necessary to connect to a particular data source. Tasks can reference the connection by its name, allowing the details of the connection to be changed or configured at runtime.

Tasks A task is an atomic work unit that performs some action. There are a couple of dozen tasks that ship in the box, ranging from the file system task (which can copy or move files) to the data transformation task. The data transformation task actually copies data; it implements the ETL features of the product.

Precedence Constraints
Tasks are linked by precedence constraints. The precedence constraint preceding a particular task must be met before that task executes. The runtime supports executing tasks in parallel if their precedence constraints so allow. Constraints may otherwise allow different paths of execution depending on the success or failure of other tasks. Together with the tasks, precedence constraints comprise the workflow of the package.
Event Handlers
A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package —such as cleaning up after errors.
Variables
Tasks may reference variables to store results, make decisions, or affect their configuration. A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server instance. In either case, the package contents is persisted in XML.

Once completed, the designer also allows the user to start the package's execution. Once started, the package may be readily debugged or monitored.

Data Transformation Services

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.

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 .