| You can think of DTS as a Data Import/Export/Transformation layer in the overall system architecture that you are deploying for at least most of your Microsoft-based applications and a few non-Microsoft applications (see Figure 20.10). DTS will allow you to "data enable" almost all of the individual applications or systems that are part of your overall implementation, such as OLTP databases, multidimensional cubes, OLAP data warehouses, Excel files, Access databases, flat files, and other heterogeneous database sources. Figure 20.10. DTS architecture.  Microsoft uses DTS Packages to capture the specific requirements of a data movement/transformation. DTS Packages contain a collection of connections, tasks , transformations, and even workflow constraints needed to fully implement a data movement/transformation requirement. DTS Packages contain one or more steps that are either executed sequentially or in parallel at package execution time. In a nutshell , when a DTS Package is executed, it will do the following: -
Connect to any identified datasource -
Copy data (and database objects if needed) -
Transform data -
Disconnect from the datasources -
Notify users, processes, or even other packages of events (such as sending an e-mail when something is done or has errors) Defined within a DTS Package are the following: -
DTS Tasks ”A discrete set of functionality, executed as a single step in a package. Tasks are the actions to be taken to accomplish the desired data transformation and movement. A task can execute any SQL statement, send mail, bulk insert data, execute an ActiveX script, or launch an external program. -
DTS Transformations ”One or more functions or operations applied against a piece of data before the data arrives at the destination. -
DTS Package Workflow ”Definable precedence constraints that allow you to link two tasks based on whether the first task executes, executes successfully, or executes unsuccessfully. Steps are the workflow wrappers for the tasks and are the means for the flow of control. A task step can run alone, parallel to another task step, or sequentially, according to precedence constraints. Precedence constraints are of three types: -
Unconditional ”It does not matter whether the preceding step failed or succeeded. -
On success ”The preceding step must have been successful for the execution of the next step. -
On failure ”Return appropriate error. A task without an associated step will not execute. DTS Designer creates a step for the task by default. However, if you write an external program using the DTS COM interface, you can easily miss the step, and the task will not run. In addition, DTS Workflows have a number of advanced features. For instance, you can add a step to a transaction, commit or roll back a transaction at this step after success or failure, close the connection on completion of this step (saving resources), use an ActiveX script for the step execution, or expose the resultset as a datasource object (DSO) rowset provider. Exposing the resultset as a DSO rowset provider lets you use the result at this step in another package, either with the OPENROWSET statement or by setting up the package as a linked server. However, after you set this option, the step does not complete, so use it only for a package that is intended to be queried from external sources. -
DTS Connectivity ”Based on OLE DB architecture. It allows you to get to data from a large variety of datasources. -
Metadata ”The capability to save the DTS Package metadata and data lineage information to Microsoft's Metadata Services. This allows Packages and anything else stored in Metadata Services to be utilized from throughout the Microsoft and external environment. DTS Tools Data Transformation Services includes several tools that simplify package creation, execution, and management: -
The DTS Import/Export Wizard can easily be used to build packages to import, export, and transform data, or to copy database objects (see Figure 20.11). Figure 20.11. Invoking DTS from the Windows program group .  -
DTS Designer (which is available through Enterprise Manager) is a GUI that lets you construct/manipulate packages containing complex workflows, multiple connections to heterogeneous datasources, and even event-driven logic (see Figure 20.12). Figure 20.12. DTS Designer workbench.  -
The Data Transformation Services entry in the SQL Server Enterprise Manager console tree, which is used to view, create, load, and execute DTS packages; to control DTS Designer settings; and to manage execution logs (see Figure 20.13). Figure 20.13. Enterprise Manager Console with DTS.   |
