MS SQL Basics Tutorial: Alerts

DTS Designer, like DTS Wizard, is the import, export, and transformation of data between homogeneous or heterogeneous data sources. However, DTS Designer is a graphical tool that makes it easier and easier to create and edit DTS packages, and provides more powerful features than DTS Wizard.

DTS Designer contains several key concepts, such as connectivity, data transfer, packages, precedence constraints, tasks, which are helpful in understanding how DTS Designer works.

18.3.1 Create DTS Designer Package

In the package, you need to specify not only the source and destination data sources, but also multiple steps, tasks performed by each step, and, in some cases, step precedence constraints. To create a package using DTS Designer First add a connection, each containing the target connection and the source connection, indicating the OLE DB provider data source in the connection, and then defining the data transformation between the source and destination connections. You can then define the tasks that the package will perform, or customize the task, and finally decide whether to run the package or store it for later use.

The following is an example of how to use DTS Designer to create a package by specifically converting the authors table. And, in this case, we want to merge the authors au_fname columns and au_lname columns into the au_name columns of the target table desauthors.

18.3.2 Add a connection

The source and destination connections are first added to the package, and the driver for the data source is indicated in each connection, although the requirements of different OLE DB providers are different.

(1) After starting SQL Server Enterprise Manager, log on to the specified server, right-click the Datatransformation Services folder, select the new package option in the pop-up menu, and open DTS package & Lt New package> dialog box.

(2) Click the Data menu item in the main menu, select the Microsoft OLE DB Provider for SQL Server option in the Drop-down menu, and open the Connection Properties dialog box, as shown in Figure 18-17.