Using SQL Server's process, because often the data from different locations together, or copy data to multiple locations, so the data export, import is a very common operation. We can use BCP command to accomplish this task But remember BCP command format is a headache grind, although you can view the help file, so we need powerful and simple tool to accomplish this task, Data Transformation Services DTS provides this support. This article will introduce the basic situation of DTS, DTS to discuss the various building components and how to complete the data conversion services.
Data Transformation Services Description
In order to support business decisions, many organizations need to analyze the data together. But usually the data is always stored in different formats in different places. Some may be a text file, although some have a table structure but does not belong to the same data source, these data greatly hampered focus. SQL Server has provided us with gratifying component, that is Data Transformation Services (DTS). DTS itself contains several tools and provides an interface to implement in any OLE DB data sources support among import, export or transfer data, and make this work easy and efficient. This means that not only SQL Server data source in the data between the dump, but also to Sybase, Oracle, Informix under the data passed to SQL Server.
Using Data Transformation Services (DTS) may at any OLE DB, ODBC driver data source or the text between this article and the SQL Server Import, export or transfer data. Specific features: data import, export services
Through reading and writing data to exchange data between different applications. For example, can be a text file or Microsoft Access database into SQL Server, you can export data from SQL Server to any OLE DB data source. Conversion data
The so-called pass is not reach the goal of the data against the data before the data source to a series of operations. Such as DTS data source from the source to allow one or more columns to calculate the new column value, and then stored in the target database.
Transfer database objects
In the case of heterogeneous data sources DTS, the built-in tool can only transfer data between the source table definition and data. If you want to transfer other database objects, such as indexes, constraints, views, we must define a task, resulting in the target database on the implementation of those tasks included in the SQL statement, SQL statements are used to create these database objects. 18.1.2 DTS structure
DTS data import, export or transfer is defined as an object can be stored, that package or as packages. Each package is including one or multiple tasks with a certain order workflow. Each task can copy data from one data source to the target data source or use of Active Scripting transform data or perform SQL statements or run an external program. You can also transfer SQL Server data source between database objects.
Package object used to create and store steps that define a series of task execution order and the implementation of tasks necessary details. Package also includes the source column object, target out and about in the data transmission process to a letter to manipulate data
Package can be stored in the DTS COM storage file structure, msdb database, or Microsoft Repository in.
The following tools can be run through the package, they are: dtsrun tools, DTS Designer, DTS Import, Export Wizard, SQL Server Agent to run the planning work, call the DTS package using the Execute method of the COM object applications.
Package is the top-level object that contains three types of underlying objects: connections, tasks, steps.
Link
Connection defines the source and target data (data source or file) information including data format information and location, and security authentication password. DTS package from time to include or contain multiple connections. Used to connect the tasks are:
DTS Data Pump task of implementing data-driven query task of SQL tasks customized tasks;
There are three types of connection object data source with
Data source to connect the source and destination are defined OLE DB data sources. This information includes the server name, data format and location, and security authentication password. The first task of using the connection responsible for creating the connection. If you use the OLE DB ODBC provider, you can also define ODBC data connection source information.
File connection defines the source and destination files. This information includes file format and location. Microsoft Data Connection object
Microsoft Data Connection object, or load the data connection file (*. udl), or OLE DB provider to set the data connection file attributes.
Mission
DTS package each contain one or more tasks, each task is data transfer (transmission) processing the work item. Types of tasks, including: implementation of the SQL task: that the implementation of SQL statements; Data Pump tasks of the task: the definition for the Data Pump operation of the source and target data sources and data conversion. Data Pump from the source and destination OLE DB data source and transform data between the copy; ActiveX script execution: ActiveX, VB, Jscript or Perscript script. Those who support the operation of the script can be run; implementation of processing tasks (Execute Process task): refers to the implementation of external procedures; bulk insert refers to the implementation of: SQL Server grant copy operation; send the message: Using SQL Mail to send pager or e-mail; data-driven inquiry: OLE DB data source implementation of advanced data transfer between; convert SQL Server objects: from the SQL Server OLE DB data source to another copy the same data source object, such as tables, indexes, views.
Step
Step object defines the order of task execution, and implementation of a task is dependent on the results of the previous task. If a task object is not associated with the steps, then it will not be executed. Can set the operating conditions for a step, it is executed only in certain conditions, to improve the performance of the implementation, a number of steps can be executed in parallel.
An important feature of the steps are the steps to priority constraints. Pre-defined priority constraints step by step the conditions must be met only after the implementation of the current step, by step task priority constraint can control the execution order. There are three kinds of priority constraints:
Complete: that after the completion of the previous step execution of the current step, regardless of its success; success: that only the successful implementation of the previous step before the implementation of the current step; failure: that when the previous steps in the implementation of the current step failed. A step may have multiple priority constraints, only the first step satisfy all the constraints before they can perform the current step.