DTS building components and how to complete data Transformation Services

In the process of using SQL Server, the export of data, import is an extremely common operation because of the frequent need to centralize data from multiple different locations or to replicate data to multiple locations. We can use the bcp command to do this, but remember that the command format for BCP is a pain in the neck, Although you can view the Help files, we need powerful and simple tools to do this, and Data Transformation Services DTS provides this support. So this chapter describes the basics of DTS, discusses the various building components of DTS, and how to complete the Data Transformation Services.

Introduction to Data Transformation Services

To support enterprise decision-making, many organizations need to centralize data for analysis. But usually the data is stored in different places in different formats. Some may be text files, some of which have a table structure but do not belong to the same data source, which greatly hinders the centralized processing of data. SQL Server provides us with a comforting component, Data Transformation Services (DTS). DTS itself contains multiple tools and provides interfaces to enable the import, export, or delivery of data between any data sources that support OLE DB, and makes this work simple and efficient. This means that data can be dumped not only between SQL Server data sources, but also from Sybase, Oracle, and Informix, to SQL Server.

The use of Data Transformation Services (DTS) may import, export, or pass in information between any OLE DB, ODBC driver's data source, or text and SQL Server. The specific performance in:

Import and export services for data

Exchange data between different applications by reading and writing data. For example, you can import a text file or a Microsoft Access database to SQL Server, or export data from SQL Server to any OLE DB data source.

converting data

Passing is a series of operations on data that is taken before the data source has reached the destination. For example, DTS allows new column values to be computed from one or more columns of the source data source, and then stores them in the destination database.

Passing database objects

In the case of heterogeneous data sources DTS, built-in tools can only pass table definitions and data between data sources. If you want to pass other database objects, such as indexes, constraints, and views, you must define a task to execute the SQL statements that are included in the task on the target database, and SQL statements are used to create the database objects.

DTS defines data import, export, or delivery as an object that can be stored, that is, a package or a package. Each package is a workflow that includes one or more tasks in a certain order. Each task can copy data from a data source to a target data source or use Active Scripting to transform data or execute SQL statements or run external programs. You can also pass database objects between SQL Server data sources.

Package objects are used to create and store steps that define the sequence in which a sequence of tasks is performed and the necessary details to perform a task. The package object also includes source columns, target columns, and information about how to manipulate the data during the data transfer process.

Packages can be stored in stored files in a DTS COM structure, in the msdb database, or in Microsoft Repository.

You can run packages by using the following tools: The DTSRun tool, DTS Designer, DTS Import, Export Wizard, SQL Server Agent to run a planning job, and use the Execute method to invoke a DTS package object's COM application.

A package is a top-level object that contains three underlying objects: connections, tasks, steps.

Connection

A connection defines information about source and target data (data source or file), including data format and location, and security-authenticated passwords. A DTS package does not contain or contain multiple connections. Tasks that use connections are:

DTS Data Pump task performs SQL task data Driven Query task customization task;